I'm doing some account cleanup in a number of directories to prepare for a metadirectory implementation. AD has been as painful as anything to work with. Here's some code that you can use to convert the UTC time that AD likes to use to something a bit more usable. (I dumped everything into Access so I could write a bunch of queries against all of the directories)
I'm having it return 1/1/1601 if the record doesn't exist (i.e. for lastlogontimestamp, the user has never logged on). You could have it return whatever you wanted to though..
Function UTC2Normal(utcDate) As Date
If utcDate <> "" Then
intvartype = VarType(utcDate)
'utcDate = CDbl(utcDate)
intutcDate1 = utcDate - 1.16444736E+17
intutcdate2 = intutcDate1 / 10000000
intutcDate3 = intutcDate2 / 1440
intutcdate4 = intutcDate3 / 60
'148012 = days from 1601 til today (3/31/06)
'13238 = days from 1970 til today (3/31/06)
'"d" compare is MUCH faster
'UTC2Normal = DateAdd("s", intutcdate2, "1970-01-01")
UTC2Normal = DateAdd("d", intutcdate4, "1970-01-01")
Else
UTC2Normal = "1/1/1601"
End If
End Function
Here's some sample SQL syntax to utilize it:
SELECT ADUsers.lastLogonTimestamp, utc2Normal(ADUsers.lastlogontimestamp) AS LastLogonFriendly, *
FROM ADUsers;
If you don't care about seconds, just days, use the line in blue instead of the line in green. It is much faster.
To my everlasting shame, it took me about 4 hours to get this working correctly. I kept trying to get it to work using straight SQL but I'm not sure if it was even possible. After beating my head against the wall for way too long I stepped out for a smoke and my brain starting thinking about other options. Presto! Problem solved.