Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Convert FILETIME to SYSTEM time using T-SQL

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

Print | posted on Monday, February 08, 2010 5:55 PM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 ]

Feedback

Gravatar

# re: Convert FILETIME to SYSTEM time using T-SQL


This is my first time i visit here. I found so many interesting stuff in your blog especially its discussion. I guess I am not the only one having all the enjoyment here! keep up the good work.
5/21/2010 11:28 AM | convert miles to km
Gravatar

# re: Convert FILETIME to SYSTEM time using T-SQL

Get ready to go airborne. With over 30 explosive jumping moves, you won't be spending much time on the ground during this highly intense cardio routine. Plyometrics, also known as jump training, has been proven to dramatically improve athletic performance. If your sport involves a ring, rink, field, court, or track, then this training will give you the edge. Just be prepared to "Bring It" for a full hour when you leap into this workout, because there is no letting up.
6/7/2011 9:30 AM | Supra For Sale
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET