Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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.
Date#() alone won't work because you can only provide one formatting per Date#() function.
Hey ... one more around the same topic.. any idea about the attached scenario .. its mixed format of Date in date and number format
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?