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

Question

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 for this? If not, can someone help me figure out how to implement this conversion procedure as UDF (or just Transact-SQL)? Here is what I know:

• FILETIME is a 64-bit value representing the number of 100 nanosecond intervals since January 1, 1601 (UTC) (after MSDN: FILETIME Structure ).
• SQL Server base time starts from 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 sure if they are 100% accurate), but could not find anything about the inverse conversion. Even a general idea (or algorithm) will help.

Answer 1

Well, 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 to 1 second, which is good with me (I could not make it more accurate due to data overflow). I used the TimeAndDate web tool to calculate the duration between dates.

What do you think?

Answer 2

2 The time of the SQL Server era starts from 1900-01-01 00:00:00 (per SELECT CAST (0 as DATETIME).

No, this is a base date, date and time starts from 1753

select cast('17800122' as datetime) 

1780-01-22 00: 00: 00.000

, , ... gregorian Julian ( , - 1753)