Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

phill_gilchrist
New Contributor III

CONVERT INTEGER8 VALUE TO DATE

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.

1 Solution

Accepted Solutions

Re: CONVERT INTEGER8 VALUE TO DATE

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)


talk is cheap, supply exceeds demand
7 Replies

Re: CONVERT INTEGER8 VALUE TO DATE

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.


talk is cheap, supply exceeds demand
phill_gilchrist
New Contributor III

Re: CONVERT INTEGER8 VALUE TO DATE

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.

MVP
MVP

Re: CONVERT INTEGER8 VALUE TO DATE

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: CONVERT INTEGER8 VALUE TO DATE

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


talk is cheap, supply exceeds demand
phill_gilchrist
New Contributor III

Re: CONVERT INTEGER8 VALUE TO DATE

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

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)


talk is cheap, supply exceeds demand
phill_gilchrist
New Contributor III

Re: CONVERT INTEGER8 VALUE TO DATE

Perfect!

Thank you.

Community Browser