As mentioned in the first article in this series, properly typed data columns are important for consumers of the datasets, because proper types allow consumers to benefit from all the built-in date features. Date columns allow you to use the column to filter on a date range, to decide what part of the date is displayed by using the Show By property, to extract individual parts of the dates, and to create custom formats for individual visualizations. The tips in this article aim to help you deal with those ugly use cases that you thought were too hard to handle.
AM/PM Date Formats
The first article in this series demonstrates how to deal with columns containing date formats that are supported for conversion. However, there are cases where the format isn’t supported by the Convert to Date function, and therefore, an alternate method to convert these more complex cases is needed.
The following example shows a classic case in which the hours in the date are in 12-hour format (hh) with an am/pm marker at the end (such as 07/24/2022 11:42:33 PM). If you attempt to add a custom format for this in the Convert to Date dialog, you see the following error message:
The limitation here is that the Convert to Date action doesn’t support 12 hour times. Fret not; there’s a simple workaround for this limitation. Use the TO_DATETIME function in the Formula editor to convert your column into a date. This function converts columns containing string literals of date time format to a DateTime data type in Oracle Analytics. It sounds technical, but don’t worry. The video below walks you thru it.
The required syntax is very simple:
TO_DATETIME(Column, ‘DateTime_Format’)
Where:
Column is the column in your dataset containing the date time strings you need to convert
DateTime_Format is the format of the date time strings in your column. For example, if your column contains dates that look like “02/14/2022 07:00:00 PM,” then enter mm/dd/yyyy hh/mi/ss ap.
For this example:
mm represents month
dd represents day
yyyy represents year
hh represents hour
mi represents minutes
ss represents seconds
ap represents the AM/PM indicator in your date time strings
If your date time strings contain milliseconds, you can use SSS.
Note that you also need to match the separators (the common ones are slash (/), dash (-), or periods (.) ) as well as matching any spacing such as the space between the date and the time.
Also add these two optional but very important error handlers at the end:
null_on_error: This parameter handles any strings that don’t match the specified format and simply return a null or empty value instead of throwing an error.
Allow_variable_digits: This parameter automatically adds missing leading zeroes to the date string to allow it to match the specified format. To continue with the previous example, the function automatically adjusts missing leading zeroes in date time strings (such as 2/14/2022 07:00:00 PM) and returns a value such as 02/14/2022 07:00:00 PM.
With the two optional error handling parameters, the full formula looks like this:
TO_DATETIME(Column, ‘DateTime_Format’, null_on_error, allow_variable_digits)
And applied to our previous example, it looks like this:
TO_DATETIME(MyColumn, ‘mm/dd/yyyy hh:mi:ss ap’, null_on_error, allow_variable_digits)
To convert a column, you can choose to either edit the column in place as in the previous example by selecting Edit from the column menu or to create a column by clicking the Create Column button. The action displays the Formula Editor. In the Formula Editor, enter the function shown above with the format matching your strings and voila! The column is now in a date time data type. When the column has a date time data type, you can use the properties to set the default format as described in this article: Four Oracle Analytics Dataset Editor Tricks Every Dataset Author Should Know
This video shows you the steps to convert the AM/PM dates using the TO_DATETIME function in the Formula Editor.
As you can see, the TO_DATETIME function is extremely powerful and flexible. I encourage you to use the above quick reference guide of the format characters that are allowed by the function and try it yourself. You can use these in various combinations to convert your string columns to date time format.
Wrangling the Date Time String Prior to Converting to Date Time Type
What do you do when dealing with non-standard date time formats; for example when a date time uses full month names that aren’t handled directly by the TO_DATETIME function? Don’t worry; you can do a little data wrangling by standardizing the month names into numbers before using the date time function. You can do it all at once in a single formula, but it’s easier and much clearer to understand if you standardize the month names first and then convert the result to a date. Let’s use an example where the date has a fully spelled out month such as February 02, 2022 07:30:00 PM.
The first step is to convert all months to their equivalent month number (for example, January is 01, February is 02, and so on). There are various ways to do this in the Dataset Editor; but for this example, let’s use a single case statement that replaces inline the month with the corresponding month number. If you want to use the formula below, simply replace DateColumn with your column.
CASE
WHEN DateColumn like ‘January%’ THEN Replace(DateColumn,’January’,’01’)
WHEN DateColumn like ‘February%’ THEN Replace(DateColumn,’February’,’02’)
WHEN DateColumn like ‘March%’ THEN Replace(DateColumn,’March’,’03’)
WHEN DateColumn like ‘April%’ THEN Replace(DateColumn,’April’,’04’)
WHEN DateColumn like ‘May%’ THEN Replace(DateColumn,’May’,’05’)
WHEN DateColumn like ‘June%’ THEN Replace(DateColumn,’June’,’06’)
WHEN DateColumn like ‘July%’ THEN Replace(DateColumn,’July’,’07’)
WHEN DateColumn like ‘August%’ THEN Replace(DateColumn,’August’,’08’)
WHEN DateColumn like ‘September%’ THEN Replace(DateColumn,’September’,’09’)
WHEN DateColumn like ‘October%’ THEN Replace(DateColumn,’October’,’10’)
WHEN DateColumn like ‘November%’ THEN Replace(DateColumn,’November’,’11’)
WHEN DateColumn like ‘December%’ THEN Replace(DateColumn,’December’,’12’)
ELSE DateColumn END
The result of this formula is that the sample date “February 02, 2022 07:30:00 PM” is converted to “02 02, 2022 07:30:00 PM.” After you’ve applied the formula, proceed with the TO_DATETIME function, ensuring that you use the right format characters in the right order. For our example, the standardize date of “February 02, 2022 07:30:00 PM” is now “02 02, 2022 07:30:00 PM” and the corresponding format is “mm dd, yyyy hh:mi:ss ap.” The formula to convert the new standardized string is:
TO_DATETIME(DateColumn,’ ‘mm dd, yyyy hh:mi:ss ap’, null_on_error, allow_variable_digits)
See this video for the entire sequence.
Bonus Feature – These Tips and Tricks Also Work in Data Flows!
You’ll be glad to know that you can use all of the previous instructions to convert text strings to date time in Data Flows! See this video to learn how to use the TO_DATETIME function using the Data Flow Transform Node.
Call to Action
I hope that you found some of these tips useful in helping you quickly deal with date columns while preparing your dataset for analysis. Now that you know about these helpful tricks in the Oracle Analytics Dataset Editor, I challenge you to go out and try them yourself! Whether you’re working with small spreadsheets or big tables from a data warehouse, there’s nothing better than spending less time wrangling data and spending more time where the value is — analyzing your data! And make sure to stay tuned for additional blogs with more tips and tricks!
For additional information, see Adjust the Display for Format of a Date or Number Column.