Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
NZFei
Partner - Specialist
Partner - Specialist

How to convert date format from strange to normal

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!

31 Replies
NZFei
Partner - Specialist
Partner - Specialist
Author

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.

NZFei
Partner - Specialist
Partner - Specialist
Author

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/  

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Updating Steve's example:

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

-Rob

NZFei
Partner - Specialist
Partner - Specialist
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

NZFei
Partner - Specialist
Partner - Specialist
Author

Thank you very much Rob!

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

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

rverschuren
Contributor II
Contributor II

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é

NZFei
Partner - Specialist
Partner - Specialist
Author

Thank you very much Rene.

Not applicable

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