Mix date formats - cleansing

[Anonymous]

Hi all,

I have a date column and my values are in mixed format.

e.g. one value is in format MM/D/YYYY and another is D/MM/YYYY (ie date and month position isn't known). Could this data be cleansed by using any combination of date and date# function?

[sunny_talwar · Accepted solution]

You will need to use Alt() combined with Date#() function to fix the issue.

Date(Alt(Date#(Date, 'FirstFormat'), Date#(Date, 'SecondFormat'), 'RequiredFormat') as Date

[Anonymous]

Hi Sunny,

thanks for the info. however I kind of had this idea of using Alt function. But I am just wondering is there any way of doing it without ALT function. I mean just by using Date and Date# .. I believe No.

[sunny_talwar]

Date#() alone won't work because you can only provide one formatting per Date#() function.

[Anonymous]

Hey ... one more around the same topic.. any idea about the attached scenario .. its mixed format of Date in date and number format

[sunny_talwar]

Try this:

Date(Alt(Date(DateField), Date#(DateField, 'DD/MM/YYYY'))) as DateField

If this doesn't work, would you be able to share a sample?