topic Convert date format in QlikView

Convert date format

Thu, 10 Mar 2011 14:15:56 GMT

Hello,

I need to convert dates from Tue Nov 24 14:36:20 CET 2009

to something like 24.11.2009

Date and date# is not working as expected...

Any ideas?

Thanks in advance,

Eva

Convert date format

Miguel_Angel_Baeyens — Thu, 10 Mar 2011 14:49:53 GMT

Hello Eva,

Unless any other has some cleaner (and above all, shorter) expression, I'd do some cleanup to get the proper timestamp:

=Date(Date#(Right(Replace('Tue Nov 24 14:36:20 CET 2009', Mid('Tue Nov 24 14:36:20 CET 2009', Index('Tue Nov 24 14:36:20 CET 2009', ' ', -2), Len('Tue Nov 24 14:36:20 CET 2009') - Index('Tue Nov 24 14:36:20 CET 2009', ' ', -1)), ''), Index('Tue Nov 24 14:36:20 CET 2009', ' ', -2)), 'MMM DD hh:mm:ss YYYY'), 'DD.MM.YYYY')

I'm likely missed some commas or closed parentheses.

Convert date format

Thu, 10 Mar 2011 15:16:59 GMT

Hi Miguel,

it works 🙂 thanks a lot for your help!

Regards,

Eva

Convert date format

suniljain — Thu, 10 Mar 2011 15:33:51 GMT

Replace String By Your Field Name

=Date(Makedate(Trim(subfield('Tue Nov 24 14:36:20 CET 2009',' ',6)), Num(Month(date#(subfield('Tue Nov 24 14:36:20 CET 2009',' ',2),'MMM'))), Trim(subfield('Tue Nov 24 14:36:20 CET 2009',' ',3))),'DD.MM.YYYY')

Convert date format

rwunderlich — Fri, 11 Mar 2011 06:05:55 GMT

How about

=date(
date#(
subfield('Tue Nov 24 14:36:20 CET 2009',' ',2)
& subfield('Tue Nov 24 14:36:20 CET 2009',' ',3)
& subfield('Tue Nov 24 14:36:20 CET 2009',' ',6)
,'MMMDDYYYY')
)