How to convert DATETIME to 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 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.

Answer 1

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?

Answer 2

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

No, that is the base date, datetime starts at 1753

run this

select cast('17800122' as datetime) 

output

1780-01-22 00:00:00.000

But this is still less than filetime so you need to add that...however remember the gregorian and Julian calendars (also the reason that datetime starts at 1753)

Answer 3

The accepted answer work well, but will crash for date above 19 January 2038. Either use DATEDIFF_BIG instead of DATEDIFF if you are on SQL Server 2016 or above, or use the following correction

CREATE FUNCTION [dbo].[fnDateTimeToFileTime]
(
    @DateTime AS DATETIME
)
RETURNS
    BIGINT
BEGIN

IF @DateTime IS NULL
    RETURN NULL

DECLARE @MsecBetween1601And1970 BIGINT
DECLARE @MsecBetween1970AndDate BIGINT

DECLARE @MaxNumberDayBeforeOverflowDateDiff int;
SET @MaxNumberDayBeforeOverflowDateDiff  = 24855; --SELECT DATEDIFF(day, CAST('1970-01-01 00:00:00' as DATETIME), CAST('2038-01-19 00:00:00' as DATETIME))

DECLARE @nbMaxDaysBetween1970AndDate int;
SET @nbMaxDaysBetween1970AndDate = DATEDIFF(day, CAST('1970-01-01 00:00:00' as DATETIME), @DateTime) / @MaxNumberDayBeforeOverflowDateDiff;

DECLARE @moduloResteDay int
SET @moduloResteDay = DATEDIFF(day, CAST('1970-01-01 00:00:00' as DATETIME), @DateTime) % @MaxNumberDayBeforeOverflowDateDiff;

DECLARE @nbSecondBefore19700101And20380119 bigint = 2147472000;
SET @MsecBetween1601And1970 = 11644473600000;

DECLARE @DateTimeModulo datetime;
SET @DateTimeModulo = DATEADD(day, -@nbMaxDaysBetween1970AndDate * @MaxNumberDayBeforeOverflowDateDiff, @DateTime)


SET @MsecBetween1970AndDate = CAST(CAST(@nbMaxDaysBetween1970AndDate as bigint) * @nbSecondBefore19700101And20380119 + 
    DATEDIFF(ss, CAST('1970-01-01 00:00:00' as DATETIME), @DateTimeModulo) as bigint)* 
        CAST(1000 AS BIGINT)

RETURN (@MsecBetween1601And1970 + @MsecBetween1970AndDate) * CAST(10000 AS BIGINT) 
END