Qlik Community

QlikView Management

Discussion Board for collaboration on QlikView Management.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Partner
Partner

How to convert date format from strange to normal

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.

Partner
Partner

Re: How to convert date format from strange to normal

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/  

MVP & Luminary
MVP & Luminary

How to convert date format from strange to normal

Updating Steve's example:

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

-Rob

Partner
Partner

Re: How to convert date format from strange to normal

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

MVP & Luminary
MVP & Luminary

How to convert date format from strange to normal

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

Partner
Partner

Re: How to convert date format from strange to normal

Thank you very much Rob!

MVP & Luminary
MVP & Luminary

How to convert date format from strange to normal

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
New Contributor II

Re: How to convert date format from strange to normal

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é

View solution in original post

Partner
Partner

Re: How to convert date format from strange to normal

Thank you very much Rene.

Not applicable

Re: How to convert date format from strange to normal

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