SQL, Active Directory, and lastLogonTimestamp

There’s a fair bit of advice out there about converting the miserable integer8 (ANSI) date format into something you can use, but for some reason, none of it quite works for me.

I have been using MS SQL SERVER 2005, which may or may not be a specific part of the problem.

ANSI dates begin 1/1/1601, while Microsoft dates have, until recently, begun 1/1/1900.  Also, I believe MS dates are/were measured in millisecond, but I’m not sure.  I do know that the ANSI timestamp is represented in units of 100 nanoseconds since 1/1/1601, which is where we start.

If you grab the lastLogonTimestamp from Active Directory (say, by doing a csvde -m -f output.csv and the using the Jet.4.0 connector to represent the directory as a linked server’s … Continue reading