topic Re: Change date format in QlikView

Change date format

Tue, 29 Oct 2013 11:56:17 GMT

Dear all,

How can I change below dateformat into a usable one:

Sep 30 2013 12:58AM

I already have the time covered, but I cannot seem to get the date converted.

Re: Change date format

israrkhan — Tue, 29 Oct 2013 11:58:21 GMT

try

=Date(Floor(DateField), 'DD/MM/YYYY') AS DATE

Re: Change date format

janardhan — Tue, 29 Oct 2013 12:01:54 GMT

Hi ,

Go to properties , in Number Tab select which expression u want to convert date format and select Date in Number format settings , from there u will convert date format as u like.

I Hope this will helps.

Re: Change date format

Jason_Michaelides — Tue, 29 Oct 2013 12:04:41 GMT

You may need to force QV to recognise your field as a date-time first:

Date(Date#(Left(DateField,11),'MMM DD YYYY'),'DD/MM/YYYY')

This will only work if you know DateField is always MMM DD YYYY hh:mm

Hope this helps,

Jason

Re: Change date format

Tue, 29 Oct 2013 13:51:27 GMT

Yes, Now I get the date in a useable format. But there is always a next question... to really calculate with this I need the timestamp as well. And if I use this field in the timestamp formula I get strange dates. Any thoughts?

Re: Change date format

Jason_Michaelides — Tue, 29 Oct 2013 13:54:44 GMT

Try,

Date(TimeStamp#(DateField,'MMM DD YYYY hh:mmtt'),'DD/MM/YYYY')

Hope this helps,

Jason

Re: Change date format

Mon, 04 Nov 2013 11:42:27 GMT

Dear Jason,

This works if the format is Oct 30 2013 10:09PM, but it goes false if any of the data is in another format, like:

Nov 1 2013 10:09PM or

Oct 30 2013 9:09PM

Any ideas how to tackle this?

Re: Change date format

Jason_Michaelides — Mon, 04 Nov 2013 11:45:42 GMT

Date(TimeStamp#(DateField,'MMM D YYYY h:mmtt'),'DD/MM/YYYY')

Re: Change date format

Mon, 04 Nov 2013 12:14:58 GMT

No, that does not work...

Re: Change date format

Jason_Michaelides — Mon, 04 Nov 2013 12:16:21 GMT

What does it return?

Re: Re: Change date format

Mon, 04 Nov 2013 12:26:19 GMT

It turns blank.

Please see attached for the example.

Re: Change date format

Jason_Michaelides — Mon, 04 Nov 2013 12:31:15 GMT

You have extra spaces in the format. This works:

Date(TimeStamp#(Replace(Time,' ',' '),'MMM D YYYY hh:mmtt'),'DD-MM-YYYY hh:mm:ss')

The replace() swaps all double-spaces with a single.

Re: Change date format

Tue, 05 Nov 2013 10:45:52 GMT

Now it works. Thanks!