[h4]More DateTime info...[/h4]

This time I teased out the internal representation of the DateTime data type. First I ran the following:

Declare @d DateTime;

Set @d='1753-01-01 00:00:00.000';

Select Cast(@d as VarBinary),Cast(@d as BigInt);

And received:

0xFFFF2E4600000000 | -53690

Immediately I noticed that 0xFFFF2E4600000000 is not equivalent to -53690! In fact -53690 is the decimal representation of the first 4 hexadecimal bytes! The Cast to VarBinary seemed to know that @d was 8 bytes long, why didn't the Cast to BigInt have the same length info? So then I tried this:

Declare @d DateTime;

Set @d='1753-01-01 00:00:00.000';

Select Cast(@d as VarBinary),Cast(@d as BigInt),Cast(Cast(@d as VarBinary) as BigInt);

And received:

0xFFFF2E4600000000 | -53690 | -230596794122240

Now that's more like it. 0xFFFF2E4600000000 is equivalent to -230596794122240! Next, based on a hunch from a previous post on this thread I ran:

Declare @d DateTime;

Set @d='1900-01-01 00:00:00.000';

Select Cast(@d as VarBinary),Cast(@d as BigInt),Cast(Cast(@d as VarBinary) as BigInt);

And received:

0x0000000000000000| 0 | 0

It looks like the internal representation is broken down in the following manor:

Bytes 1 - 4 represent the date component (CCYY-MM-DD)

Bytes 5-8 represent the time component (HH:MM:SS.mmm)

But what are their units and intervals? Well, the following query will tell us that:

Declare @d DateTime;

Set @d='1753-01-01 00:00:00.000';

Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);

Set @d='1753-01-02 00:00:00.001';

Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);

Set @d='1753-01-02 00:00:00.003';

Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);

0xFFFF2E4600000000 | -53690 | 0

0xFFFF2E4700000000 | -53689 | 0

0xFFFF2E4700000001 | -53689 | 1

Notice how I modified the date-time string before each query and notice the resultant output values. From an analysis of these values it can be seen that:

Bytes 1 - 4 represent the date component in days, however, the range does not start with 0, it starts with -53690.

Bytes 5-8 represent the time component in 3.33~ millisecond counts, i.e., this count increments by 1 for every 3.33~ milliseconds that pass; the range starts at 0.

So what are the actual ranges for days and 3.33~ millisecond counts? Lets run another query:

Declare @d DateTime;

Set @d='1753-01-01 00:00:00.000';

Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);

Set @d='9999-12-31 23:59:59.997';

Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);

0xFFFF2E4600000000 | -53690 | 0

0x002D247F018B81FF | 2958463 | 25919999

From these results we can see that the day range is -53690 through 2958463, or 3012154 (don't forget to add 1 for the zero crossing) which is 8246 years, the full span from years 1753 to 9999.

From these results we can also see that the 3.33~ millisecond count range is 0 through 25919999, or 25920000 counts which is 86400000 milliseconds (25920000*3.33~) or 1 days worth of milliseconds.

So, there you have it. You could use this knowledge to craft an ElapsedTime function that will work across the full spectrum of time from 1753-01-01 00:00:00.000 through 9999-12-31 23:59:59.997 with some reasoned logic and judicious use of SubString, Cast, & Convert. It probably won't be the fastest performer but it will give you the full time span available without any "out-of-range datetime value" exceptions!

Happy Casting!