Data Quality Management Checks - Handling Different Date Formats

While System generated Reports largely have a uniform Date format for Date fields in the Report, Non-System Reports may have Dates which are manually entered by the Business User creating a situation with varying Date formats. Such fields need to be cleaned up and generalised to facilitate Date based analytics in IDEA.

 

Let us examine this case with an example ~

 

You have a data dump in IDEA with a sample field 'Incident Date' which has the sample Dates - 05.01.19, 05-JAN-2019 and 05/01/2019

 

Now 'Incident Date' appears in IDEA as a Character field.

 

The need of the moment is to convert this field having varying Date formats to a consistent and uniform Date format in IDEA so as to analyze 'Incidents' reported on 1st January 2019

 

This can be done by appending a Virtual Date field titled 'Incident Date IDEA' with the criteria ~

 

@compif(@len(Incident Date) = 8, @ctod(Incident Date, "DD.MM.YY"), @len(Incident Date) = 11, @ctod(Incident Date, "DD-MMM-YYYY"), @len(Incident Date) = 10, @ctod(Incident Date, "DD/MM/YYYY"))

 

The criteria above in essence maps the length of the Incident Date in a Character format to the appropriate Date mask for conversion from Character to Date.

 

So 05.01.19 has a length of 8 and the mask for conversion would be DD.MM.YY

 

In the criteria above @Len() is used to capture the length of the Incident Date and @ctod() is used to convert Incident Date in a Character format to Incident Date in a Date format.

 

Best Regards

 

Group Admin Team