How to convert DATETIME to FILETIME value in T-SQL?
- by Alek Davis
I need to convert a SQL Server DATETIME value to FILETIME in a T-SQL SELECT statement (on SQL Server 2000). Is there a built-in function to do this? If not, can someone help me figure out how to implement this conversion routine as a UDF (or just plain Transact-SQL)? Here is what I know:
FILETIME is 64-bit value representing the number of 100-nanosecond intervals since
January 1, 1601 (UTC) (per MSDN: FILETIME Structure).
SQL Server time era starts on 1900-01-01 00:00:00 (per SELECT CAST(0 as DATETIME).
I found several examples showing how to convert FILETIME values to T-SQL DATETIME (I'm not 100% sure they are accurate, though), but could not find anything about reverse conversion. Even the general idea (or algorithm) would help.