help with AD timestamp

hopkinsc — Mon, 20 Jan 2014 17:20:23 GMT

hi, i need to convert a timestamp from the active directory into a readable date/time.

can anyone help please?

the value of the 'lastLogon' field is as follows...

130048049010771403

I have no idea how to convert this to a timestamp. i have found a way of extracting the date part but not the time.

The date is is as follows...

IF(lastLogon > 0, (date(left(lastLogon,11)/86400-109205,'YYYY-MM-DD')),'') as [Last Logon]

Any help would be appreciated.

Thanks

Re: help with AD timestamp

maxgro — Mon, 20 Jan 2014 20:35:33 GMT

Using this two links

I think it should be

=//130048049010771403 / (8.64 * pow(10,11)) - 109205 & chr(10) &

date(130048049010771403 / (8.64 * pow(10,11)) - 109205, 'DD-MM-YYYY hh:mm:ss')

& chr(10) &

date(128271382742968750 / (8.64 * pow(10,11)) - 109205, 'DD-MM-YYYY hh:mm:ss')

The result of the 2 expression above is (GMT)

Hope it helps you