CONVERT INTEGER8 VALUE TO DATE

[Anonymous]

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.

[Gysbert_Wassenaar]

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.

[Anonymous]

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.

[jonathandienst]

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

[Gysbert_Wassenaar]

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

[Anonymous]

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?

[Gysbert_Wassenaar · Accepted solution]

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)

[Anonymous]

Perfect!

Thank you.