如何在T-SQL中将DATETIME转换为FILETIME值？

Question

我需要在SQL Server 2000的T-SQL SELECT语句中将SQL Server DATETIME值转换为FILETIME。是否有内置函数可以实现此功能？如果没有，是否有人可以帮助我找出如何将此转换例程实现为UDF（或仅使用Transact-SQL）？这是我所知道的：

1. FILETIME是64位值，表示自1601年1月1日（UTC）以来的100纳秒间隔数（每MSDN：FILETIME结构）。
2. SQL Server基准时间从1900-01-01 00:00:00开始（每SELECT CAST（0 as DATETIME））。

我发现了几个示例，展示了如何将FILETIME值转换为T-SQL DATETIME（尽管我不确定它们是否准确），但找不到任何关于反向转换的内容。即使是一般的想法（或算法）也会有所帮助。

- Alek Davis

Answer 1

好的，我认为我可以自己实现这个。以下是函数：

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

它似乎精确到1秒，这对我来说可以接受（由于数据溢出，我无法使其更加精确）。我使用TimeAndDate 网站工具来计算日期之间的持续时间。

你认为呢？

- Alek Davis

Answer 2

SQL Server的时间纪元始于1900年01月01日00:00:00（使用SELECT CAST(0 as DATETIME)进行验证）。

不，那是基准日期，datetime从1753年开始。

运行以下命令：

select cast('17800122' as datetime) 

输出

1780年01月22日00:00:00.000

但这仍然比文件时间少，因此您需要加上它...但是要记住公历和儒略历（也是datetime从1753开始的原因）。

- SQLMenace

---

哦，太好了，现在我更加困惑了。 :-) - Alek Davis

至少在SQL Server 2008中，datetime2类型可以表示到公元1年。 - SQLMenace

Answer 3

被接受的答案效果很好，但对于2038年1月19日之后的日期会崩溃。如果您使用的是SQL Server 2016或更高版本，请改用DATEDIFF_BIG，否则请使用以下更正方法。

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

- Ant