Handling multiple date formats in one load file

[mikegrattan]

I've got a file with two date formats in it and I'd like to convert it to one format.  I've copied some sample data below.  As you can see, some of the dates are DD-MM-YYYY and some are D/M/YYYY.  I'd like to format the date as MM/DD/YYYY, keep the timestamp format and also be able to create a separate field that is just the date in MM/DD/YYYY format.

31-01-2017 12:00:00

31-01-2017 12:00:00

31-01-2017 12:00:00

31-01-2017 12:00:00

31-01-2017 12:00:00

31-01-2017 12:00:00

31-01-2017 12:00:00

31-01-2017 12:00:00

31-01-2017 12:00:00

1/2/2017 0:00

1/2/2017 0:00

1/2/2017 0:00

1/2/2017 0:00

1/2/2017 0:00

1/2/2017 0:00

1/2/2017 0:00

1/2/2017 0:00

1/2/2017 0:00

[sunny_talwar · Accepted solution]

May be this

Date(Floor(Alt(Num(DateField), Num(Date#(DateField, 'DD-MM-YYYY hh:mm:ss')), Num(Date#(DateField, 'D/M/YYYY h:mm')))), 'MM/DD/YYYY') as NewDateField

[mikegrattan]

Very nice, that worked great!

Thank you.

[Koniki]

date(alt(date,Date#(date,'DD-MM-YYYY'),Date#(date,'MM-DD-YYYY'),Date#(date,'DD/MM/YYYY'),Date#(date,'MM/DD/YYYY')),'MM/DD/YYYY') as newdates,

[davyqliks]

this is great, Thanks

Daniel