|
|
Monday, February 08, 2010
Today I answered a question where OP wanted to convert a 64-bit filetime value to a system time. After a little reading about FILETIME, I learnt that the number is a value for 100 ns passed since January 1st 1601. This gets little tricky since the normal date zero for SQL Server is January 1st 1900.
But with a little arithmetic things worked out. The most cumbersome part was the INT limit for passing parameters to DATEADD function, but that was easy to overcome too. However, I am not convinced the actual number for the nanosecond value is returned "reversed" from system API originally. If that's not the case, just skip the REVERSE function in the derived table d.
See the code below
DECLARE @Sample TABLE
(
f CHAR(5),
lastmod CHAR(16),
filedate DATETIME
)
INSERT @Sample
VALUES ('File1', '0d4081fc676aac10', '2/5/2010 10:20'),
('File2', '0c03f1fc676aac10', '2/5/2010 10:20'),
('File3', '0c18164bf2f7ac10', '12/17/2009 10:43'),
('File4', '0bbe32fc676aac10', '2/5/2010 10:20'),
('File5', '0a6a82fc676aac10', '2/5/2010 10:20'),
('File6', '46bd5b9873db9c10', '4/14/2009 14:30'),
('File7', '0a88d2fc676aac10', '2/5/2010 10:20')
;WITH cteYak
AS (
SELECT f,
filedate,
CAST('' AS XML).value('xs:hexBinary(sql:column("Yak"))', 'BINARY(8)') AS Bin
FROM (
SELECT f,
filedate,
REVERSE(lastmod) AS Yak
FROM @Sample
) AS d
)
SELECT f,
filedate,
DATEADD(SECOND, Sec, DATEADD(MINUTE, Peso, 0)) AS Peso
FROM (
SELECT f,
filedate,
CAST((CAST(Bin AS BIGINT) - CAST(94354848000000000.0 AS BIGINT)) / 10000000 / 60 AS INT) AS Peso,
CAST((CAST(Bin AS BIGINT) - CAST(94354848000000000.0 AS BIGINT)) / 10000000 % 60 AS INT) AS Sec
FROM cteYak
) AS d
|