Peter Larsson Blog

Patron Saint of Lost Yaks

The internal storage of a DATETIME value

SELECT  [Now],
        BinaryFormat,
        SUBSTRING(BinaryFormat, 1, 4) AS DayPart,
        SUBSTRING(BinaryFormat, 5, 4) AS TimePart,
        CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT) AS [Days],
        DATEADD(DAY, CAST(SUBSTRING(BinaryFormat, 1, 4) AS INT), 0) AS [Today],
        CAST(SUBSTRING(BinaryFormat, 5, 4) AS INT) AS [Ticks],
        DATEADD(MILLISECOND, 1000.E / 300.E * CAST(SUBSTRING(BinaryFormat, 5, 4) AS INT), 0) AS Peso
FROM    (
            SELECT  GETDATE() AS [Now],
                    CAST(GETDATE() AS BINARY(8)) AS BinaryFormat
        ) AS d

Legacy Comments


Naomi
2011-07-07
re: The internal storage of a DATETIME value
How are you going to convert int value into datetime?

Peso
2011-07-07
re: The internal storage of a DATETIME value
You mean an INT like 20110707? Not easily since an integer doesn't follow 12 month rule per year.