help with AD timestamp

[hopkinsc]

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

[maxgro]

Using this two links

How to convert date/time attributes in Active Directory to standard time format

https://social.technet.microsoft.com/wiki/contents/articles/12814.active-directory-lastlogontimestam...

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