Как преобразовать DATETIME в значение FILETIME в T-SQL?

Question

Мне нужно преобразовать значение DATETIME SQL Server в FILETIME в операторе SELECT T-SQL (в SQL Server 2000). Есть ли встроенная функция для этого? Если нет, может ли кто-нибудь помочь мне понять, как реализовать эту процедуру преобразования в виде UDF (или просто Transact-SQL)? Вот что я знаю:

1. FILETIME - это 64-разрядное значение, представляющее число интервалов в 100 наносекунд с 1 января 1601 года (UTC) (для MSDN: структура FILETIME).
2. Базовое время SQL Server начинается с 1900-01-01 до 00:00:00 (за SELECT CAST(0 в качестве DATETIME)).

Я нашел несколько примеров, показывающих, как преобразовать значения FILETIME в T-SQL DATETIME (хотя я не уверен на 100%, что они точны), но не смог найти ничего об обратном преобразовании. Даже общая идея (или алгоритм) поможет.

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 для расчета продолжительности между датами.

Как вы думаете?

Answer 2

2 Время эры SQL Server начинается 1900-01-01 00:00:00 (за SELECT CAST(0 в качестве DATETIME).

Нет, это базовая дата, дата-время начинается в 1753

запустить это

select cast('17800122' as datetime) 

выход

1780-01-22 00:00:00.000

Но это все же меньше времени файла, поэтому вам нужно добавить это... однако помните григорианский и юлианский календари (также причина, по которой дата и время начинаются в 1753 году)

Answer 3

Принятый ответ работает хорошо, но выйдет из строя для даты выше 19 января 2038 года. Используйте DATEDIFF_BIG вместо DATEDIFF, если вы используете SQL Server 2016 или более поздней версии, или используйте следующее исправление

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