0

I am loading a Qlikview CalData PGO XML file into a SQL table.

Dates in this XML are stored as a 64 bit HEX string and I need to convert it to a date.

How do I do that?

Example

40e58f2c4153d0f9 should be converted to 18/11/2020 09:11:29

I have seen some sample code within qlikview, which is

daystart(date($(HEX64CONVERT(ToBeDeleted))))
Improve this question
2
  • It looks like this is explored on the Qlikview forums (1, 2). Could you poke around there to refine your question a bit? Certainly asking how to go HexString->(Big)Integer->DateTime could be an answerable platform-specific question - but it's not immediately apparent to me that your sample values follow that convention.
    – Peter Vandivier
    Nov 25, 2020 at 13:00
  • I have a method to convert this in Qlikview, I need to convert this in SQL.
    – mouliin
    Nov 25, 2020 at 14:02

2 Answers 2

Reset to default
1

This value is encrypted by QlikView to prevent you from tampering with the value.

Some fields in the generated XML file i.e. "LastUsed", are encrypted for security reasons, thus cannot be converted into readable dates using date() conversion. The generated XML files should be used for troubleshooting only.

https://community.qlik.com/t5/Support-Knowledge-Base/Reading-PGO-contents-and-CAL-assignments-PGO-to-XML/ta-p/1710604?_ga=2.160240829.1496857553.1606312645-1955769053.1606312645

Presumably this is part of how they track and enforce their sofware licensing agreements.

Improve this answer
0

I believe something like this should work for you (though you'd have to switch from seconds to milliseconds if your dates are more granular than seconds):

SELECT dateadd(s, convert(bigint, convert(varbinary(64), '0x40e58f2c4153d0f9', 1)) / 1000, convert(datetime, '1-1-1970 00:00:00'))

(I have to verify this solution still when I'm in front of a computer and will update accordingly.)

Improve this answer
2
  • It gives me an Arithmetic overflow error
    – mouliin
    Nov 25, 2020 at 14:01
  • @mouliin Same, that's because DATEADD() function only accepts INT, not BIGINT. Once I find an alternate way to accomplish that part, I'll update my answer.
    – J.D.
    Nov 25, 2020 at 14:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.