t-sqldatetimefiletime

How to convert DATETIME to FILETIME value in T-SQL?


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:

  1. FILETIME is 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (UTC) (per MSDN: FILETIME Structure).
  2. SQL Server base time 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.


Solution

  • Okay, I think I was able to implement this myself. Here is the function:

    IF EXISTS 
    (
        SELECT 1
        FROM   sysobjects 
        WHERE  id   = OBJECT_ID('[dbo].[fnDateTimeToFileTime]')
          AND  type = 'FN'
    )
    BEGIN
        DROP FUNCTION [dbo].[fnDateTimeToFileTime]
    END
    GO
    
    -- Create function.
    CREATE FUNCTION [dbo].[fnDateTimeToFileTime]
    (
        @DateTime AS DATETIME
    )
    RETURNS
        BIGINT
    BEGIN
    
    IF @DateTime IS NULL
        RETURN NULL
    
    DECLARE @MsecBetween1601And1970 BIGINT
    DECLARE @MsecBetween1970AndDate BIGINT
    
    SET @MsecBetween1601And1970 = 11644473600000
    
    SET @MsecBetween1970AndDate = 
        DATEDIFF(ss, CAST('1970-01-01 00:00:00' as DATETIME), @DateTime) * 
            CAST(1000 AS BIGINT)
    
    RETURN (@MsecBetween1601And1970 + @MsecBetween1970AndDate) * CAST(10000 AS BIGINT)  
    END
    GO
    
    IF @@ERROR = 0
        GRANT EXECUTE ON [dbo].[fnDateTimeToFileTime] TO Public 
    GO
    

    It seems to be accurate up to 1 second, which is okay with me (I could not make it more accurate due to data overflow). I used the TimeAndDate web tool to calculate the durations between dates.

    What do you think?