SQLFileTimeToDatetime

I never thought that I would have to deal with something like this, as with all new things learned.

There is a data type called "FileTime". It's an 8 byte binary value representing the number of hundreds of nanoseconds since 1/1/1601. This causes many a problem when attempting to find out what date is really represented by, for example, what is 0x01C91768B47194F0? Meaningless to me! So I convert it to bigint so I can get a better grasp as to what it really means. One PRINT Convert(bigint, 0x01C91768B47194F0) later, and I find it means 128659802828150000. Yeah. Muuuch easier to understand. If you're Spock! Or Data! Or a Mentat! Or a computer! Or...or...oh, you get the idea.

Next the job was to convert that to something meaningful as far as a readable datetime value. So just add 128659802828150000 * 100 nanoseconds to 1/1/1601! Problem: 1/1/1601 is not a date SQL Server can understand since datetime</tt> values minimum is 1/1/1753</tt>. And working with numbers as big as 128659802828150000</tt> is juuust outside of SQL Servers useful datetime</tt> functions. Even its not-so-useful ones!

After much cursing and surfing, I stumbled upon someone's answer to this very problem. (And I Stumbled it.) It can be found here: | http://bit.ly/7VEl8

The following is the result. It creates two functions, one to operate on the standard binary value of FileTime and one to operate on a bigint</tt>.

Try it out. Works like a charm. Something had to unravel the FileTime nightmare!