topic CONVERT INTEGER8 VALUE TO DATE in QlikView

CONVERT INTEGER8 VALUE TO DATE

Anonymous — Mon, 17 Mar 2014 17:08:12 GMT

I'm trying to interrogate Active Directory for the lastLogon.

I'm pulling the data through, but it's converting it to the date / datetime I'm after help with.

I see there's a few methods out there using SQL or Powershell through calculation, but trying those calculations in qlikview doesn't give the desired results.

Any ideas / thoughts / suggestions gleefully welcomed.

Thanks.

Re: CONVERT INTEGER8 VALUE TO DATE

Gysbert_Wassenaar — Mon, 17 Mar 2014 17:26:32 GMT

According to msdn.microsoft.com/en-us/library/ms676823(VS.85).aspx it's stored as a large integer that represents the number of 100-nanosecond intervals since January 1, 1601 (UTC). So divide by 10.000*3.600*24 = 864.000.000 and then subtract the number of days between January 1, 1601 and December 30, 1899... = 109.205.

Re: CONVERT INTEGER8 VALUE TO DATE

Anonymous — Tue, 18 Mar 2014 09:35:20 GMT

Thanks for responding Gysbert.

Unfortunately though your solution is not working in my instance.

An example output I've got is my lastLogon is coming back as 130359240658783000 and following your steps, doesn't get me back to number that I can interpret as date / datetime.

Re: CONVERT INTEGER8 VALUE TO DATE

jonathandienst — Tue, 18 Mar 2014 09:51:02 GMT

Gysbert

I think that should be divide by 864,000,000,000 for 100 ns intervals (10,000,000 * 3,600 etc).

Phill

Using that divisor, I get to day # 41673.75 which is 3 Feb 2014 18:00:00.

HTH

Jonathan

Re: CONVERT INTEGER8 VALUE TO DATE

Gysbert_Wassenaar — Tue, 18 Mar 2014 09:55:57 GMT

Jonathan is correct. I was off by a factor 1000 (overlooking the microseconds). The result then matches the result of w32tm.exe /ntte 130359240658783000.

Re: CONVERT INTEGER8 VALUE TO DATE

Anonymous — Tue, 18 Mar 2014 10:08:32 GMT

Brilliant. Thanks to you both, that gets me to an answer, but is there any reason why Qlikview, (version 11.2) doesn't seem to handle the expression?

Re: CONVERT INTEGER8 VALUE TO DATE

Gysbert_Wassenaar — Tue, 18 Mar 2014 10:51:57 GMT

I'm not sure what expression you're talking about. I'm guessing that you're trying to get Qlikview to handle a number with more than 14 digits. Qlikview supports only 64-bit IEEE floating numbers. To work around that you can do the calculation in the SQL statement you use to get the data from Active Directory. Or you could cut off the last 7 digits first, then divide by 86.400 and then subtract 109.205: =timestamp(left('130359240658783000',11)/86400 - 109205)

Re: CONVERT INTEGER8 VALUE TO DATE

Anonymous — Tue, 18 Mar 2014 10:58:57 GMT

Perfect!

Thank you.