Converting Columns to DateTime in Oracle Analytics Cloud

Oracle Analytics Cloud (OAC) offers powerful in-house data profiling capabilities. This blog describes how to convert a column from an attribute field  to a DateTime field . 

If this blog is the first resource you’re looking at, please check whether the Convert to Date option described in the Oracle Documentation solves your issue. For many situations, it takes just a few clicks for Oracle Analytics Cloud to detect the DateTime format and fix your column. Sometimes, Oracle Analytics Cloud doesn’t detect a format and that’s where this blog will come in handy.

Unsupported DateTime Formats

First, let’s talk about what doesn’t work. The following table shows the attribute formats that Oracle Analytics Cloud doesn’t convert to DateTime. The left column lists the unsupported formats, and the right column shows you format characters that you might have seen in other guides or programming languages.

Converting Columns Using “Convert to Date”

Now let’s look at the steps to convert a column attribute to DateTime and the formats that do work. Let’s start with the simplest conversion method – Convert to Date.

1. In Oracle Analytics Cloud, navigate to the column that you wish to change in the data pane.
2. Right-click and select Convert to Date. 

3. A custom option dialog displays with an empty box. In the empty box, enter the date format that you want to use. Refer to the tables below to determine the formats you can enter in the box.
4. Click Add Step to convert your column to a DateTime field.

Examples – Convert to Date

Converting Columns Using “Edit Column”

The Edit Column method offers less formatting options but greater control when dealing with inconsistent data. This method requires you to use the To_DateTime() function.

1. As before, navigate to the column that you wish to change.
2. Right-click, and this time select Edit.
   

   

3. The Edit Column dialog is displayed with a text box that contains the name of your column in blue. In this box, move your cursor before the name of your column and type: To_DateTime(
   

   

4. After your column name, add a comma followed by the DateTime format you want to use enclosed within two single quotes. This table shows the formats you can use.

To_DateTime() Enhancers

Before showing examples of the To_DateTime() function, let’s touch on the enhancers. In general, enhancers are the reason why you use the “edit column” method rather than the “convert to date” method. When you type the To_DateTime() function you have the option to add Allow_Variable_Digits and (or) Null_On_Error to the end of the function. For example:

To_DateTime(Column Name, 'format characters', Allow_Variable_Digits, Null_On_Error) 

Let’s explore what these options do and see some examples.

Allow_Variable_Digits

This enhancer allows the algorithm to correctly assign one-digit formats correctly, even if they’re in the same column. For example, a column containing 06/4/2017 and 06/04/2017 will come through as the same date.

Null_On_Error

This enhancer allows you to discard rows that don’t fit the format you declare. For example, if row 1 was 06/4/2017, row 2 was 06/5/2017, and row 3 was July/6/2017, using the format code MM/D/YYYY would properly convert row 1 and 2 but leave row 3 empty (only for this specific column, that is, it won’t remove data from the same row in other columns).

Caution: The modifier Null_On_Error can lead to data loss. You can avoid this data loss if you use other functions such as CAST and Replace. One of these alternatives is better than the other, as discussed below.

Examples – Edit Column

Avoiding Data Loss

Casting

The first function is CAST(). Casting is defined as changing a data type of a value (or a null value) to another data type. In Oracle Analytics Cloud, the function is listed as: CAST(expr AS type), where expr is any expression and type is any data type. A practical example here could be using it instead of To_DateTime(), in the following format:

CAST(SalesDate AS DATE)

It’s worth noting that the CAST() function isn’t nearly as comprehensive as the other methods already outlined in this blog. In most situations, these methods are more effective than CAST().

Replace

The second and most effective method of conversion is to use the Replace option. The Replace option lives in the same menu as Convert to Date and Edit. To access this option, right-click on your column and select Replace.

In the Replace dialog, you see four options:

• Match partial values: Replaces all instances of an original string with a new string. For example, if you enter “male” for String to replace and “M” for New String, Oracle Analytics Cloud changes “male” to “M” and “female” to “feM”.
• Match entire values only: With this option, if you enter “male” for String to replace and “M” for New String, Oracle Analytics Cloud changes “male” to “M” but doesn’t replace “female” with “feM”.
• Use regular expressions: Uses Regex. Oracle Analytics Cloud uses Perl Compatible Regular Expression. You can find more information on how to use Regex here.
• Replace null or missing values: The option isn’t useful in relation to this blog but it allows you to fill null or empty rows with a value.

Replace is going to be the most useful by helping you transform strings from unconvertable DateTime formats into convertible formats. For example, starting with the string “March 08/21” and turning “March “ into “03/”. Thus, transforming the row into “03/08/21”.

Conclusion

This blog is shorter than I originally intended but I hope it was useful. For more information, check out our Oracle Analytics Cloud resources:

• Documentation,tutorials, and videos
• Blogs
• Support forums

Or  chat with a member of our team!