Specify the formats for the String data, to successfully convert it to Date or Date/Time.

by Nov 4, 2016

My String field in Visual Analytics Workbook contains few rows of date data in multiple formats.
Converting this String dimension to a Date or Date/Time, by choosing Change Data Type > Data and time do not seem to work for all records. Records present in formats such as “6/25/16 8:15 pm” are successfully converted to dates, while records present in a format such as “25/6/16 8:15 pm” returns (null).

Note: Currently I am using en_US locale.

What formats should I specify for the String data, to successfully convert it to Date or Date/Time?

Response

swapnil chaudhari over 6 years ago
When converting String to Date or Date/Time formats supported for parsing, depends on the locale of your current ADS instance. If a format works with this locale, then the string is converted, else “(null)” is displayed.

To check locale of ADS
1. On the ADS navigation bar, click Help > Support Information.
2. From the Operating Environment details, you can verify details of current ADS instance.

For example, you should see details as “Linux (3.13.0-86-generic, amd64) / UTF-8 / en / US / Oracle Corporation 1.8.0_40-b26
Memory: Max=704,643,072; Total=439,353,344; Free=278,720,464; CPUs=8”

Locale of this ADS instance is “en_US”; where “en” is language and “US” is country.
Please refer Locale.png

In the attached Locale.xlsx, I have listed the formats supported for parsing, for the various locales.
To successfully convert a String value to date or date/time, the String value should match one of the supported formats for your given Locale.

For example, if your string data is “11/21/2016” and locale is “en_US”, then this record is successfully converted to date as “en_US” locale supports parsing of the format “MM/dd/yyyy”. However, if some other locale does not support parsing of this format; such as, β€œde_DE”, then on converting to date “null” is displayed in the Workbook.

swapnil chaudhari over 6 years ago
The attached Locale.xlsx lists date/time formats supported for parsing in 32 locales. If your current ADS locale does not match any locale mentioned in this list, then ADS tries to match on language. It uses the locale it finds as the first match.
For example, if you are using a locale, en_IN or en_PH, which is not listed in the xlsx, then ADS attempts to find a match on language (en) and stops when it finds the first match and uses formats of the found locale for date/time parsing.

The order of searching for locale:

1) ADS searches for an exact match of the locale, listed in the attached excel sheet.
2) If the exact match fails, it tries to match only on language in the attached list and stops when it finds the first match and uses that locale.
3) If it fails to find a match, it defaults to en_US.