Convert String to Date MMM DD YYYY hh:mm

[samuel_lin]

Hello Qlik Geeks,

I am using Qlik Sense. I have a date string that has the format: 'mmm dd yyyy hh:mmAM/PM'. I have read a few discussion on date string conversion and tried to convert but failed. I am hoping to drop the time and have the date format: 'DD/MM/YYYY'

a few examples of the strings to be converted

Apr  1 2014 12:00AM

Sep 30 2015 12:00AM

in data load, I tried (the column hearder is "order date":

Date(Date#([order date],'MMM DD YYYY hh:mm'),'DD/MM/YYYY') as [order date]

but couldn't get it to work. Am I missing something? please help, thank you!

Samuel

[Frank_Hartmann]

try

Date(Floor(Date#([order date],'MMM DD YYYY hh:mm')),'DD/MM/YYYY') as [order date]

hope this helps

[sunny_talwar]

May be you are missing TT for AM/PM

Date(Floor(Date#([order date],'MMM DD YYYY hh:mmTT')), 'DD/MM/YYYY') as [order date]

[samuel_lin]

Frank and Sunny,

Thank you for helping out. I tried

Date(Date#([order date],'MMM DD YYYY hh:mmtt'),'MM/DD/YYYY') as [order date]

and it worked. However, I found that only the MMM DD YYYY hh:mmtt format is converted, the MMM D YYYY hh:mmtt aren't converted...

any thought on how to handle both format?

Thanks!

[antoniotiman]

Hi Samuel,

I think the problem is 2 spaces between Apr and 1.

Try

=Date(Date#(Replace('Apr 1 2014 12:00AM',' ',' '),'MMM DD YYYY hh:mmTT'),'DD/MM/YYYY')  -> 01/04/2014

In Replace  '  '(2 Spaces),' '(1 space)

Regards,

Antonio

[samuel_lin]

Hi Antonio, but if the two spaces is taken care of, how should those with one space be handle at the same time?

Thanks,

[Anil_Babu_Samineni]

Tri this to without floor

Date(Date#([order date],'MMM DD YYYY hh:mm TT'),'DD/MM/YYYY') as [order date]

[maxgro]

Date(Floor(

  Alt(     Date#([order date],'MMM DD YYYY hh:mmTT'),

             Date#([order date],'MMM  DD YYYY hh:mmTT')

     )

) , 'DD/MM/YYYY')

[samuel_lin · Accepted solution]

Thank you, everyone, for your inputs and helpful solutions!

I got this resolved using the following if statement and so far it's working well!

(If((substringcount([order date], ' '))=4,

  (Date(Date#([order date],'MMM  D YYYY hh:mmtt'),'MM/DD/YYYY')),

    (Date(Date#([order date],'MMM DD YYYY hh:mmtt'),'MM/DD/YYYY'))) ) as [order date]

Thanks!

Samuel

[antoniotiman]

It Always works, because with 1 space Replace haven't effect.

Regards,

Antonio