topic Handling multiple date formats in one load file in App Development

Handling multiple date formats in one load file

mikegrattan — Fri, 18 May 2018 17:48:49 GMT

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

1/2/2017 0:00

Re: Handling multiple date formats in one load file

sunny_talwar — Fri, 18 May 2018 17:53:26 GMT

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

Re: Handling multiple date formats in one load file

mikegrattan — Fri, 18 May 2018 20:01:26 GMT

Very nice, that worked great!

Thank you.

Re: Handling multiple date formats in one load file

Koniki — Tue, 30 Jul 2019 07:57:58 GMT

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,

Re: Handling multiple date formats in one load file

davyqliks — Tue, 21 Sep 2021 09:02:20 GMT

this is great, Thanks

Daniel