-- 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
*/
This page was last updated on May 01, 2006 04:28 PM.