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 database and the csv file as a table…), you are presented with a number like 1.28nnnnnnnnnnnnnnnE+17, which is awful.
There’s a formula out there which tells you to divide the bignum by 60, then by 100000000, then by 1440, and that’s all good, but the magic number there is divide by 864,000,000,000. This will convert 100-nanosecond slices into the number of days passed since 1/1/1601 (with the decimal portion as usual representing the fractional part of a day, so that dd.75 means 6 in the evening on day dd), and I encourage you to work through the math yourself to prove it.
But at this point, it is just a big floating-point number, and will display as such. The problem comes in when you feed that number into a function such as CONVERT(), and try to get a datetime representation. It says that somebody’s last logon was in about the year 2309, which is not likely to be true. This is where all the available guidance abandoned me, which is why I suspect that perhaps SQL SERVER 2005 does it a little differently–if it has borrowed Excel date functions from the 32-bit library, or something; I have no idea.
On a hunch I took the DATEDIFF() between 1/1/1900 and 1/1/1601, which yields 109,207. This is the number of days between those dates. I then subtracted that amount from the big float which purports to be a last logon time, and Voila! The result was yesterday’s logon (from a midnight data pull).
So for some reason, even when I brought the lastLogonTimestamp over in the fashion recommended by all of the guidance I could find, I wound up with my dates being interpreted as belonging to the wrong epoch.
