topic Re: Convert String to Date MMM DD YYYY hh:mm in QlikView

Convert String to Date MMM DD YYYY hh:mm

samuel_lin — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Convert String to Date MMM DD YYYY hh:mm

Frank_Hartmann — Mon, 03 Oct 2016 17:26:38 GMT

try

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

hope this helps

Re: Convert String to Date MMM DD YYYY hh:mm

sunny_talwar — Mon, 03 Oct 2016 17:34:19 GMT

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]

Re: Convert String to Date MMM DD YYYY hh:mm

samuel_lin — Mon, 03 Oct 2016 17:38:50 GMT

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!

Re: Convert String to Date MMM DD YYYY hh:mm

antoniotiman — Mon, 03 Oct 2016 17:39:27 GMT

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

Re: Convert String to Date MMM DD YYYY hh:mm

samuel_lin — Mon, 03 Oct 2016 17:51:02 GMT

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

Thanks,

Re: Convert String to Date MMM DD YYYY hh:mm

Anil_Babu_Samineni — Mon, 03 Oct 2016 18:35:35 GMT

Tri this to without floor

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

Re: Convert String to Date MMM DD YYYY hh:mm

maxgro — Mon, 03 Oct 2016 18:49:36 GMT

Date(Floor(

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

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

)

) , 'DD/MM/YYYY')

Re: Convert String to Date MMM DD YYYY hh:mm

samuel_lin — Mon, 03 Oct 2016 18:52:14 GMT

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

Re: Convert String to Date MMM DD YYYY hh:mm

antoniotiman — Mon, 03 Oct 2016 19:44:59 GMT

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

Regards,

Antonio