How to convert DATETIME to FILETIME value in T-SQL?
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:
- FILETIME is 64-bit value representing the number of 100-nanosecond intervals since January 1, 1601 (UTC) (per MSDN: FILETIME Structure).
- 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.
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?
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)
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
精彩评论