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:
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?