Tom Cooper
3/24/2007 12:36:00 AM
The first half is an integer specifying the number of days since Jan 1,
1900. That is, on 3/23/2007 there had been 0x000098FA or 39162 days since
1/1/1900.
So
SELECT DATEADD(d, 39162, '19000101')
will return 2007-03-23 00:00:00.000
The last half is the number of clock ticks since midnight where a clock tick
is 3 and 1/3 milliseconds. So, for example,
select cast(0x000098FA00000000 as datetime)
select cast(0x000098FA00000001 as datetime)
select cast(0x000098FA00000002 as datetime)
select cast(0x000098FA00000003 as datetime)
will return
2007-03-23 00:00:00.000
2007-03-23 00:00:00.003
2007-03-23 00:00:00.007
2007-03-23 00:00:00.010
Please note that the internal formats used to store data in SQL Server are
not documented. So, at least in theory, they might change in some future
release. Also, some bit patterns are not valid as datetimes. For example,
0x002D248000000000 (which would imply 0x002D2480 or 2,958,464 days after
1/1/1900 or Jan 1, 10000) is not a valid SQL Server date and unexpected
things can happen if you succeed in storing that value in a datetime column.
Tom
"Karch" <nospam@absotutely.com> wrote in message
news:u2DQBUabHHA.1300@TK2MSFTNGP02.phx.gbl...
> So, what is actually happening here?
>
> DECLARE @datenow as DATETIME;
>
> DECLARE @dateBinary as VARBINARY(MAX);
>
> SELECT @datenow = '3/23/2007 12:00:00AM';
>
> SELECT @dateBinary = CAST( @datenow AS VARBINARY(MAX))
>
> SELECT @dateBinary
>
> @dateBinary = '0x000098FA00000000'
>
> (using a date of '3/22/2007 12:00:00AM' will produce '0x000098F900000000'
> as the binary representation)
>
> What is this binary value that SQL Server is producing; how is it
> produced? I am trying to produce the same result in code when serializing
> a C# datetime datatype.
>
> Thanks!
>
>