Most of the times, we don’t get clean data. Especially the data from web or non-transactional systems where there is no data integrity or constraints, which will have some noise. Let’s assume that we have single date field which has multiple date formats (As shown below).

 

84_01

 

Currently, “MyDateField” field has 4 different date formats, and out of which 3 of them are not date fields (which are aligned as strings on the left hand side). There is only one correct date format with two rows.

 

84_02

 

There are many ways to convert the above string values in to TRUE date format. You can write multiple nested IF statements to evaluate the date formatting. Instead we can use the Alt() function which returns the first parameter while checking for the valid number representation. Alt() function will test if the field “MyDateField” contains a date according to any of the specified date formats. If so, it will return the original string and a valid number representation of a date. If no match is found, the text ‘No Match’ will be returned (without any valid number representation).

 

Sample Script:

 

FinalTable:

Load

MyDateField

,DATE(ALT(Date#(MyDateField,’DD/MM/YYYY’)

,Date#(MyDateField,’MMM/DD/YYYY’)

,Date#(MyDateField,’DD/MMM/YYYY’)))           AS                MyFieldInDateFormat

Resident Test;

 

Using the above script you can convert the dates into correct format. See the below example where “MyDateField is old field and “MyFieldInDateFormatis new field with correct date format.

84_03