Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
mikegrattan
Contributor II

Handling multiple date formats in one load file

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
1 Solution

Accepted Solutions

Re: Handling multiple date formats in one load file

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

3 Replies

Re: Handling multiple date formats in one load file

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
Contributor II

Re: Handling multiple date formats in one load file

Very nice, that worked great!

Thank you.

Koniki
New Contributor II

Re: Handling multiple date formats in one load file

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,