You can convert any text column to a date, time, or timestamp column.
For example, you can convert an attribute text column to a true date column.
- Open the project or the data set that includes the column you want to convert. Confirm that you’re working in the Prepare canvas.
- Hover over the column that you want to convert.
- Click Options, and select a conversion option (for example, Convert to Number, Convert to Date).You can also do this from the Data Sets page when you’re editing a data set.
- To further refine the format, select the column, and use the options on the properties pane.
- If you want to change the Source Format's default value then click Source Format and select a format. For example, 2017.01.23, 01/23/2017, Mon, Jan 23, 2017, or Mon, Jan 23, 2017 20:00:00.The Source Format field automatically displays a suggested format based on the input column text. However, if the Source Format field doesn’t display a suggested format, for example, for Sat 03/28 2017 20:10:30:222, then you can enter a custom format.
- Click Custom if you need to enter your own format into the field at the bottom of the Convert to Date/Time dialog.The custom format you enter must be in a format recognized by Oracle Business Intelligence before conversion. If you enter a custom format that isn’t recognized, an error message is displayed.
- The Hide Source Element is selected by default and hides the original source column after conversion. If you deselect this option, the original column is displayed next to the converted column after conversion.
- Click Convert to convert the text column into a date or time column.The changes you make apply to all projects using the data source with a modified date or time column.