topic Re: Date format? in QlikView

Date format?

khadeer — Mon, 09 Jul 2012 06:04:44 GMT

I am loading the data from XML file. The date format is like 40E11678DD7CC6C How i can change this date into DD-MM-YYYY

Regards,

Khadeer

Re: Date format?

Mon, 09 Jul 2012 06:16:22 GMT

Hi ,

Can u please provied a sample XML file from where you are getting this particular format of data.

Re: Date format?

khadeer — Mon, 09 Jul 2012 06:49:12 GMT

Sorry to say i am unable to provide xml file.

40E11678DD7CC6C which format it is?

Re: Date format?

Mon, 09 Jul 2012 07:00:44 GMT

Hi Khadeer,

Have a read of the following post: Re: How to convert date format from strange to normal

It's a UNIX timestamp in hexadecimal.
Conversion:
40E40633h = 1088685619
Excel formula (for example):
=(1088685619)/60/60/24+DATE(1970,1,1)
Result:
Thursday, 2004-07-01 14:40:19
posted by Stefan Bäckstrand in post I attached.

So in essence you would need to do the above converstion then format using DATE(<converted date>, 'DD-MM-YYYY') or DATE#(<converted date>, 'DD-MM-YYYY')

There are other functions as well such as Timestamp, that you could use to get the date in the right format.

Keep in mind the functions with the '#' take a text representation of a date. If the input date is not the right "type" then you will get '-' as a result. Thats where timestamp may help.

If you are confused with what i've said, please feel free to ask again.

Hope I've helped