Bob Barrows said:
Please. Stop trying to force a particular format when inserting data into
databases. Databases do not store format. They store numeric
recpresentations of dates. Access stores date/times as Doubles, with the
whole number portion representing the number of days since the seed date
and the decimal portion representing the time (.0 = midnight, .5 = noon).
SQL Server stores datetimes as paired integers, with the first integer
containing the number of days since the seed date, and the second
containing the number of milliseconds since midnight.
[None of the following is meant to detract from what Bob has said...]
That may be exactly what BOL says, but it is not, in fact, correct. The
low-order int stores the number 3 millisecond intervals since midnight, not
the number of milliseconds. This can be proven conclusively in T-SQL
script, in a number of ways; perhaps the simplest being:
select convert(datetime, '2008-01-10 01:00:00.001')
[returns 2008-01-10 01:00:00.000]
select convert(datetime, '2008-01-10 01:00:00.002')
[returns 2008-01-10 01:00:00.003]
If you are interested in more-definitive proof let me know.
A logical question might be, why am I on about this? I can only answer that
with another question: why would Microsoft choose to publish incorrect
documentation about this, over three releases of SQL Server? To me it's
unfathomable. (Note that SQL 2008 BOL no longer publishes this lie, but
neither does it correct it, it merely omits any low-level storage details.)
-Mark