topic Re: How to convert date format from strange to normal in QlikView

How to convert date format from strange to normal

NZFei — Mon, 26 Jan 2026 18:19:17 GMT

Hello, I have created a qvw file to show qlikview server information. I have ServerName, Name, NoOfNamedCALs and so on. However for three fields LastUpdated, LastUsed and ToBeDeleted, the value (should be date, aren't they?) are strange. They are 40E4067AD8153D10, 40E406245C71C71C, FE37E43C8800759C. I have tried to set the number formats for them to Date but it didn't work. Are they some kinds of date formats? How can I convert them to normal formats? Many thanks in advance!

How to convert date format from strange to normal

Jason_Michaelides — Thu, 12 Apr 2012 21:35:23 GMT

Can you post a sample of your source data? And exactly where on the server are you getting the data from?

Re: How to convert date format from strange to normal

NZFei — Thu, 12 Apr 2012 21:49:39 GMT

I got the data from CalData.pgo.xml (Because I have set PGOAsXmlAlso=1 in settings.ini, I can have CalData.pgo in xml file). It is located in server document folder. Sample is attached. Thanks a lot.

Re: How to convert date format from strange to normal

Jason_Michaelides — Thu, 12 Apr 2012 22:33:13 GMT

Can you post the xml file too?

Re: How to convert date format from strange to normal

NZFei — Thu, 12 Apr 2012 23:04:36 GMT

Thank you for your time!

Re: How to convert date format from strange to normal

Jason_Michaelides — Fri, 13 Apr 2012 08:48:37 GMT

I don't think I can be much help to be honest! I can only guess that those strings are links to another record, mabe in another of the .pgo files.

Sorry mate,

Jason

How to convert date format from strange to normal

StefanBackstrand — Mon, 07 May 2012 09:00:07 GMT

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

http://fmdiff.com/fm/timestamp.html

How to convert date format from strange to normal

NZFei — Mon, 07 May 2012 21:01:57 GMT

Thank you so much, Stefan!

How can I add any lines to QlikView Scripts to convert UNIX timestamp in hexadecimal to default date/time format? Is it possible?

Fei

How to convert date format from strange to normal

stevedark — Mon, 07 May 2012 22:07:28 GMT

Building on Stefan's post, I presume the QlikView code would be:

Date((1088685619/60/60/24)+Date#('19700101', 'YYYYMMDD'), 'DD MMM YYYY') as [My Date Field],

The second date format specifier you can change to be whatever you want, but the first one must match the format of the 1970 date.

Hope that helps,

Steve

http://www.quickintelligence.co.uk/

How to convert date format from strange to normal

NZFei — Mon, 07 May 2012 22:19:13 GMT

Steve,

Thank you very much!

The last thing is the date/time I get from CalData.pgo.xml looks like 40E4067AD8153D10, 40E406245C71C71C, FE37E43C8800759C. How can I convert them to like 1088685619 ? Any way to do it in script?

How to convert date format from strange to normal

NZFei — Mon, 07 May 2012 23:18:33 GMT

For example, the Lastupdated for a particular document is 40E4099EDEE8DD7D. I am pretty sure it is in this year 2012. However no matter how I tried to convert 40E4099EDEE8DD7D to Decimal number and calculate the date, the result is not in 2012.

Re: How to convert date format from strange to normal

NZFei — Mon, 07 May 2012 23:43:43 GMT

Maybe they are not UNIX timestamp in hexadecimal. I couldn't use any converter to convert them to dec and then add to qlikview sciprts

40E4067AD8153D10,

40E406245C71C71C,

FE37E43C8800759C.

For example through this website:

http://unixtimestamp.50x.eu/

How to convert date format from strange to normal

rwunderlich — Mon, 07 May 2012 23:46:57 GMT

Updating Steve's example:

=Date((num#('40E4099E', '(HEX)')/86400)+Date#('19700101', 'YYYYMMDD'), 'DD MMM YYYY')

-Rob

Re: How to convert date format from strange to normal

NZFei — Mon, 07 May 2012 23:53:05 GMT

Thank you very much Rob but the result is 2004, not 2012.

How to convert date format from strange to normal

rwunderlich — Tue, 08 May 2012 00:58:40 GMT

I can't comment on your expectations of the source data. But I believe the conversion is "correct". Today's date in hex unix timestamp is 4FA85E73.

-Rob

Re: How to convert date format from strange to normal

NZFei — Tue, 08 May 2012 02:49:48 GMT

Thank you very much Rob!

How to convert date format from strange to normal

stevedark — Tue, 08 May 2012 10:17:49 GMT

Sometimes application's timestamps are not actually representations of the time - just a unique string that has no value other than being a unique string.

It should be possible to find out if that is the case for this file.

- Steve

Re: How to convert date format from strange to normal

rverschuren — Fri, 05 Jul 2013 23:58:51 GMT

You can convert these strange dates (actually the dates are 64 bit hexadecimals) by using a formula I posted in http://community.qlik.com/docs/DOC-4388.

René

Re: How to convert date format from strange to normal

NZFei — Sun, 07 Jul 2013 20:56:44 GMT

Thank you very much Rene.

Re: How to convert date format from strange to normal

Thu, 05 Mar 2015 09:00:33 GMT

Hi, did you manage to convert the number 'FE37E43C8800759C' using the link René posted? I cannot make it work...