topic Re: Need help in converting Distorted Date in QlikView

Need help in converting Distorted Date

Sun, 07 Feb 2016 22:46:08 GMT

Hi all,

I have a Date like below, How could I convert into MM-DD-YYYY.

Walter

sunny_talwar — Sun, 07 Feb 2016 23:51:01 GMT

Images are not visible, would you be able to reattach?

The basic idea is to do something like this:

Date(Date#(DateField, 'YourCurrentFormatHere'), 'MM-DD-YYYY') as DateField

swuehl — Mon, 08 Feb 2016 00:35:51 GMT

If your date field shows different formats (and this is what you called distorted), you can look into Alt() function to test different date formats:

Example from HELP:

alt(

date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date'

)

In general, have a look into

Mon, 08 Feb 2016 04:53:51 GMT

There is some more data which is not shown in the image. Where you could find data as

2914

2421

Where i assumed it to be Feb 9th 2014 and Feb 4th 2021 but its not.

Thank you Swuehl.

Mon, 08 Feb 2016 05:41:49 GMT

Your attachment in the original post did not work so I am just going to explain based on an example.

Before your load script.. Set the Date and Time formate as below :

SET DateFormat='MM/DD/YYYY';

SET TimestampFormat='MM/DD/YYYY h:mm:ss[.fff] TT';

And try below in your load script.

Date([Your Date Field]) AS DateField

It works for me. It turns our client's Australian dates (DD/MM/YYYY) into American (MM/DD/YYYY)

swuehl — Mon, 08 Feb 2016 11:26:48 GMT

And how do you interpret

12212

January 22 2012 or December 2nd 2012?