Binaryworld - Dissecting datetime value storage to individual dateparts. ... [ SQL Server -> T-SQL ]

Ever wondered how SQL Server stores the datetime value in 8 bytes. Well, this code shows how to decode the values using simple mathematical operations.

Click here to copy the following block

-- Datetime storage uses 8 bytes out of which
-- 4 bytes store the number of days since '1900-01-01' &
-- 4 bytes store the number of milliseconds past midnight.
-- The accuracy of milliseconds is 1/300th of a second & we make
-- use of this fact in the calculations. For more details,
-- see BOL documentation on datetime data type.
declare @d datetime , @days int , @msec int
select @d = current_timestamp
select @d AS thisisthedatetime
/*
thisisthedatetime
------------------------------------------------------
2000-09-04 20:37:42.150
*/
-- Use substring to strip the 1st 4 bytes &
-- the next 4 bytes easily. Avoids having to juggle with
-- floating point values!
select @days = cast( substring( cast( @d as varbinary ) , 1 , 4 ) as int ) ,
    @msec = cast( substring( cast( @d as varbinary ) , 5 , 4 ) as int )
select @Days AS dayssince1900 , @msec AS msecsincemidnight
/*
dayssince1900 msecsincemidnight
------------- -----------------
    36771     22278645
*/
-- Expressions are left as is to make the calculations
-- easier to understand.
select datepart( yy , dateadd( dd, @days , '1900-01-01') ) as "Year" ,
    datepart( mm , dateadd( dd, @days , '1900-01-01') ) as "Month" ,
    datepart( dd , dateadd( dd, @days , '1900-01-01') ) as "Day" ,
    -- take remainder after each division to get hr, min, sec.
    @msec / ( 300 * 60 * 60 ) AS Hours ,
    ( @msec % ( 300 * 60 * 60 ) ) / ( 300 * 60 ) AS Minutes ,
    ( ( @msec % ( 300 * 60 * 60 ) ) % ( 300 * 60 ) ) / ( 300) AS Seconds ,
    -- for ms, have to use the fact that the accuracy is 1/300 only
    convert( int , round ( (( ( ( @msec % ( 300 * 60 * 60 ) ) % ( 300 * 60 ) )
               % ( 300 )) / 3. ) * 10 , 0 ) ) AS MilliSeconds
/*
Year    Month    Day     Hours    Minutes   Seconds   MilliSeconds
----------- ----------- ----------- ----------- ----------- ----------- ------------
    2000      9      4     20     37     42     150
*/