SQL DateTime Explored

Here is a quick rummage in the innards of Microsoft SQL Server's DateTime type - I have intentionally kept wordiness to a minimum - it's all there in the code.

print 'datetime is represented by eight bytes of data';
print '==============================================';

declare @now as datetime;
set @now = getdate();

declare @now_raw as binary( 8 );
set @now_raw = cast( @now as binary( 8 ) );

select
 @now [@now: getdate()]
, @now_raw [@now_raw: cast( @now as binary( 8 ) )];

/*****************************************************************************
*****************************************************************************/

print 'the base datetime is midnight ( as in beginning of ) 1st January 1900';
print '=====================================================================';

declare @base as datetime ;
set @base = cast( 0 as datetime );

select
 @base [@base: cast( 0 as datetime )]
, cast( @base as binary( 8 ) ) [cast( @base as binary( 8 ) )];

/*****************************************************************************
*****************************************************************************/

print 'these eight bytes represent two four byte integers';
print '==================================================';

declare @days_raw binary( 4 );
declare @ticks_raw binary( 4 );
/* note: substring( value, start, length ) is offset from 1 */
set @days_raw = substring( @now_raw, 1, 4 );
set @ticks_raw = substring( @now_raw, 5, 4 );

select
 @days_raw [@days_raw: substring( @now_raw, 1, 4 )]
, @ticks_raw [@ticks_raw: substring( @now_raw, 5, 4 )];

/*****************************************************************************
*****************************************************************************/

print 'the first represents the number of days since the base date';
print '===========================================================';

declare @days int;
set @days = cast( @days_raw as int );
select
 datediff( day, @base, @now ) [datediff( day, @base, @now )]
, @days [@days: cast( @days_raw as int )];

/*****************************************************************************
*****************************************************************************/

print 'if padded correctly it can just be cast to datetime';
print '===================================================';

select
 cast( @days_raw + 0x00000000 as datetime ) [cast( @days_raw + 0x00000000 as datetime )];

/*****************************************************************************
*****************************************************************************/

print 'the date value can be separated from the time without doing binary manipulation';
print 'note: 0 is implicitly converted to the base datetime';
print '===============================================================================';

declare @today datetime;
set @today = dateadd( day, datediff( day, 0, @now ), 0 );
select
 @today [@today: dateadd( day, datediff( day, 0, @now ), 0 )];

/*****************************************************************************
*****************************************************************************/

print 'the second is the number of ticks since midnight, a tick is 1/300 seconds long';
print '==============================================================================';

declare @ticks int;
set @ticks = cast( @ticks_raw as int );
select
 datediff( second, @today, @now ) [datediff( second, @today, @now )]
, @ticks [@ticks: cast( @ticks_raw as int )]
, @ticks / 300 [@ticks / 300]
, @ticks / 300.0 [@ticks / 300.0];

/*****************************************************************************
*****************************************************************************/

print 'a datetime can be cast to a float representing number of days since base';
print '========================================================================';

select
 cast( @now as float ) [cast( @now as float )];

/*****************************************************************************
*****************************************************************************/

print 'this can be checked by constructing a float from the days and ticks values';
print '==========================================================================';
/* note: float conversion prevents data loss during integer division */
select
 @days + ( @ticks / cast( ( 300 * 60 * 60 * 24 ) as float ) ) [@days + ( @ticks / cast( ( 300 * 60 * 60 * 24 ) as float ) )];

/*****************************************************************************
*****************************************************************************/

print 'which means that [ cast( cast( @date_time_value as float ) as datetime ) == @date_time_value ] should be invariant';
print '==================================================================================================================';

select
 @now [@now]
, cast( cast( @now as float ) as datetime ) [cast( cast( @now as float ) as datetime )]

print 'and hence that the mean of a set of datetime values can be calculated by casting to float';
print '=========================================================================================';

print 'create table #temp_date ( value datetime );'
print '';
create table #temp_date ( value datetime );

print 'insert into #temp_date ( value ) values ( getdate() );';
insert into #temp_date ( value ) values ( getdate() );
print 'insert into #temp_date ( value ) values ( ''1980-01-19'' );';
insert into #temp_date ( value ) values ( '1980-01-19' );
print 'insert into #temp_date ( value ) values ( ''2000-01-01'' );';
insert into #temp_date ( value ) values ( '2000-01-01' );

print '#temp_date:';
select t.value from #temp_date t;

select cast( avg( cast( t.value as float ) ) as datetime ) [cast( avg( cast( t.value as float ) ) as datetime )]
from #temp_date t;

drop table #temp_date;

---

Running this lot gave me the following:

datetime is represented by eight bytes of data
==============================================
@now: getdate()         @now_raw: cast( @now as binary( 8 ) )
----------------------- -------------------------------------
2010-11-22 13:51:35.577 0x00009E3600E46761

(1 row(s) affected)

the base datetime is midnight ( as in beginning of ) 1st January 1900
=====================================================================
@base: cast( 0 as datetime ) cast( @base as binary( 8 ) )
---------------------------- ----------------------------
1900-01-01 00:00:00.000      0x0000000000000000

(1 row(s) affected)

these eight bytes represent two four byte integers
==================================================
@days_raw: substring( @now_raw, 1, 4 ) @ticks_raw: substring( @now_raw, 5, 4 )
-------------------------------------- ---------------------------------------
0x00009E36                             0x00E46761

(1 row(s) affected)

the first represents the number of days since the base date
===========================================================
datediff( day, @base, @now ) @days: cast( @days_raw as int )
---------------------------- -------------------------------
40502                        40502

(1 row(s) affected)

if padded correctly it can just be cast to datetime
===================================================
cast( @days_raw + 0x00000000 as datetime )
------------------------------------------
2010-11-22 00:00:00.000

(1 row(s) affected)

the date value can be separated from the time without doing binary manipulation
note: 0 is implicitly converted to the base datetime
===============================================================================
@today: dateadd( day, datediff( day, 0, @now ), 0 )
---------------------------------------------------
2010-11-22 00:00:00.000

(1 row(s) affected)

the second is the number of ticks since midnight, a tick is 1/300 seconds long
==============================================================================
datediff( second, @today, @now ) @ticks: cast( @ticks_raw as int ) @ticks / 300 @ticks / 300.0
-------------------------------- --------------------------------- ------------ ---------------------------------------
49895                            14968673                          49895        49895.576666

(1 row(s) affected)

a datetime can be cast to a float representing number of days since base
========================================================================
cast( @now as float )
----------------------
40502.5774951003

(1 row(s) affected)

this can be checked by constructing a float from the days and ticks values
==========================================================================
@days + ( @ticks / cast( ( 300 * 60 * 60 * 24 ) as float ) )
------------------------------------------------------------
40502.5774951003

(1 row(s) affected)

which means that [ cast( cast( @date_time_value as float ) as datetime ) == @date_time_value ] should be invariant
==================================================================================================================
@now                    cast( cast( @now as float ) as datetime )
----------------------- -----------------------------------------
2010-11-22 13:51:35.577 2010-11-22 13:51:35.577

(1 row(s) affected)

and hence that the mean of a set of datetime values can be calculated by casting to float
=========================================================================================
create table #temp_date ( value datetime );
 
insert into #temp_date ( value ) values ( getdate() );

(1 row(s) affected)
insert into #temp_date ( value ) values ( '1980-01-19' );

(1 row(s) affected)
insert into #temp_date ( value ) values ( '2000-01-01' );

(1 row(s) affected)
#temp_date:
value
-----------------------
2010-11-22 13:51:35.577
1980-01-19 00:00:00.000
2000-01-01 00:00:00.000

(3 row(s) affected)

cast( avg( cast( t.value as float ) ) as datetime )
---------------------------------------------------
1996-12-24 04:37:11.857

(1 row(s) affected)

---

Note: rounding errors sometimes creep into the purportedly invariant condition stated above so watch for the stray 5 milliseconds or so!

Controlling Text Input Form Submission

The content of this post is not really ground-breaking stuff but it's the kind of thing I can see myself referring back to and it may be of use to someone else.

I recently implemented an incremental search box on a web application and wanted to control what effect the <ENTER> key had on the form's behaviour. The application is built so that it will work if JavaScript is disabled - the user simply enters a search term and clicks search ( or presses <ENTER> ) to get a list of matches; the client side script supplements this by serving up results as the user types ( fairly standard behaviour ).

As well as handling individual keystrokes I wanted to launch the top search term on <ENTER>. In order to achieve this I had to disable the browser's default behaviour of submitting the form when <ENTER> is pressed in a single line text input.

Below is the neatest solution I could find that worked in all ( major ) browsers ( detail omitted ):

var searchBox
  = document.getElementById( 'searchBox' );

// ....

// use content of search box to refine results
searchBox.onkeyup = function( e ) {
  e = e || window.event; // ensure event obj
  
  if ( e.keyCode == 13/*RETURN*/ ) {
    // use topmost search result
  }
  else {
    // update results using searchBox.value
  }
}

// prevent form submission when <ENTER> is pressed
searchBox.onkeypress = function( e ) {
  e = e || window.event; // ensure event obj

  return e.keyCode != 13/*ENTER*/;
}

I found that the onkeyup event was the best place to use the input's value and the onkeypress event was the best place to control the form's behaviour - I tried other combinations as well as using the event's cancelBubble property and stopPropagation() method as described here http://www.quirksmode.org/js/introevents.html but in the end only the above actually did what I wanted.