SQL Server's datetype datatype is, internally, two 32-bit words (integers). The high-order word is the offset in days since the epoch (zero point) of the internal calendar used by SQL Server: that epoch is 1 January 1900 00:00:00.000. The low-order word is the offset in milliseconds since start-of-day (00:00:00.000/midnight).
For historic reasons, while the precision of the low-order word is 1 millisecond; the accuracy is 1/300th second (!?). That means that any given point-in-time is rounded to an increment of of 0, 3 or 7 milliseconds.
To do the conversion in the manner in which SQL Server does it, do the following: Take the milliseconds portion of the actual time under consideration, a value from 0-999, modulo 100. That gives you the low order digit, a value from 0-9. So if the current time is 23:57:23.559, the milliseconds component is 559. Modulo 100 you get 9.
- Values 0 and 1 are "rounded down" to 0.
- Values 2, 3 and 4 are "rounded down"" to 3.
- Values 5, 6, 7 and 8 are "rounded down" to 7.
- A value of 9 is rounded UP to 0. This has a rather unpleasant and nasty side effect: if the milliseconds portion of the time is 999, it ticks up 1 millisecond. This means that the time 23:59:59.999 is rounded up to THE NEXT DAY. So conversion of '31 Dec 2010 23:59:59.999' yields a datetime value of...1 January 2011 00:00:00.000.
Brilliant! Or something.
See the "Remarks" section under SQL Server 2005 BOL here: http://msdn.microsoft.com/en-us/library/ms187819(v=SQL.90).aspx
The upshot of all of this is that you can't do the obvious check for a date range/time period...something like
where myDateColumn between '1 September 2011 00:00:00.000'
and '30 September 2011 23:59:59.999'
as that potentially brings in a [small] chunk of data from the next period. And you can't say
where myDateColumn between '1 September 2011 00:00'
and '30 September 2011 23:59:59'
as that potentially excludes data that belongs in the period. Instead, you must say something like
where myDateColumn >= '1 September 2011 00:00:00.000' and myDateColumn < '1 October 2011 00:00:00.000', or
where myDateColumn >= '1 September 2011 00:00:00.000' and myDateColumn <= '30 September 2011 23:59:59.997'
It should be noted that smalldatetime, which has a precision of 1 minute, exhibits the same bogus behaviour: if the seconds component of the time under consideration is 29.998 seconds or less, it is rounded down to the nearest minute; if 29.999 or higher, it is rounded UP to the next minute, so the value 31 Dec 2010 23:59:30.000' winds up as asmalldatetimevalue of1 Jan 2011 00:00:00`.
This has all sorts of implications, especially WRT to billing systems and the like.
I would say that if precision is important to you, store your date/time values in SQL Server as strings in ISO 8601 format, something like 2011-10-30T23:59:55.1234 (or the equivalent 'compact' form (20111030T235955.1234). ISO 8601 collates and compares properly; it converts easily and it is human-readable. Even better split it out into two columns — one for date (2011-10-30) and one for time (23:59:55.1234). Then add a third, computed column to put it all together:
create table foo
(
...
transaction_date char(10) not null ,
transaction_time char(12) not null ,
...
iso8601_transaction_datetime as transaction_date + 'T' + transaction_time ,
...
)
A pretty good summary of ISO 8601 is at http://www.cl.cam.ac.uk/~mgk25/iso-time.html. Wikipedia also has pretty good information: http://en.wikipedia.org/wiki/ISO_8601.
