Mixed Dates in Field

[qlikfresh]

Hi Community, 

I have a question about converting a date field which has a mixed format

If you look at the screenshot below, DATE_SAMPLEDoriginal is mixed with MM/DD/YYYY hh:mm format & numerical format

What is the best way to convert this to DD/MMM/YYYY format? 

Thanks!

[AshutoshBhumkar]

Hi, You can use ALT() for mixed date formats.

e.g 

Alt(
Date(Timestamp#(Date,'MM/DD/YYYY hh:mm'),'DD/MMM/YYYY'),
Date(Date#(Date),'DD/MMM/YYYY')
) as Date

Thanks,

Ashutosh

[qlikfresh]

Hey @AshutoshBhumkar 

Thanks for the quick reply, I tried that formula and looks like its not working for the numerics 

[AshutoshBhumkar]

Try using this 

Alt(
Date(Date#(Date,'MM/DD/YYYY hh:mm'),'DD-MMM-YYYY'),
Date(Num(Date),'DD-MMM-YYYY')

) as Date

[qlikfresh]

Hi @AshutoshBhumkar 

Nearly there! It looks like it is converting it the wrong way round, it reads 09-Jan-2021 but should say 01-Sep-2021. Is there a way I can flip the month and day? 

Thanks!

[AshutoshBhumkar · Accepted solution]

Okay, you can add this then 

Date(Date#(Date(Num(Floor(Date))),'D/M/YYYY'),'DD-MMM-YYYY') in alt()

like :

Alt(
Date(Date#(Date,'MM/DD/YYYY hh:mm'),'DD-MMM-YYYY'),
Date(Date#(Date(Num(Floor(Date))),'D/M/YYYY'),'DD-MMM-YYYY'),
Date(Num(Date),'DD-MMM-YYYY')) as Date1

[qlikfresh]

@AshutoshBhumkar , thank you 🙂 I would not have gotten that on my own. Much appreciated