代码之家 › 专栏 › 技术社区 › Alek Davis

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

filetime datetime tsql

Alek Davis · 技术社区 · 16 年前

我需要转换一个SQL Server 日期时间 价值在一个 T-SQL语言 SELECT语句(在SQL Server 2000上)。是否有一个内置函数来执行此操作?如果没有,有人能帮我弄清楚如何实现这个转换例程作为一个自定义项(或只是普通的Transact-SQL)?我知道的是:

MSDN: FILETIME Structure ).

我找到了几个示例,演示了如何将FILETIME值转换为T-SQL DATETIME(不过,我不能100%确定它们是否准确),但是找不到任何有关反向转换的内容。即使是一般的想法(或算法)也会有所帮助。

2 回复 | 直到 16 年前

Alek Davis 16 年前

好吧,我想我自己也能做到。函数如下:

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 web tool 计算日期之间的持续时间。

你怎么认为?

SQLMenace 16 年前

1900-01-01 00:00:00 作为日期时间)。

运行此

select cast('17800122' as datetime)

输出

1780-01-22 00:00:00.000

Ant 5 年前

接受的答案很有效,但会在2038年1月19日以上的日期崩溃如果您使用的是SQL Server 2016或更高版本,请使用DATEDIFF\u BIG而不是DATEDIFF,或者使用以下更正

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