[h4]DateDiff tricks and the missing 147 years[/h4]
Data-type DateTime has the following properties:
It can represent any valid date/time between 1753-01-01 00:00:00.000 and 9999-12-31 23:9:59.998.
Declare
@st DateTime,
@et DateTime;
Select
@st='1753-01-01 00:00:00.000',
@et='9999-12-31 23:59:59.998';
Mess with the dates a bit an you will see that these two are the upper and lower boundaries.
Nothing new here, BOL tells us that.
It is internally represented by two 32-bit integers.
Declare
@st DateTime,
@et DateTime;
Select
@st='1753-01-01 00:00:00.000',
@et='9999-12-31 23:59:59.997';
Select
Cast(@st as VarBinary)[@st as Hex],
Cast(@et as VarBinary)[@et as Hex];
Select
SubString(Cast(@st as VarBinary),1,4)[@st 1st Int as Hex],
SubString(Cast(@st as VarBinary),5,4)[@st 2nd Int as Hex],
SubString(Cast(@et as VarBinary),1,4)[@et 1st Int as Hex],
SubString(Cast(@et as VarBinary),5,4)[@et 2nd Int as Hex]
Select
Cast(SubString(Cast(@st as VarBinary),1,4) as Int)[@st 1st Int as Int],
Cast(SubString(Cast(@st as VarBinary),5,4) as Int)[@st 2nd Int as Int],
Cast(SubString(Cast(@et as VarBinary),1,4) as Int)[@et 1st Int as Int],
Cast(SubString(Cast(@et as VarBinary),5,4) as Int)[@et 2nd Int as Int];
Oddly, the lowest date/time boundary (1753-01-01 00:00:00.00) is not internally code as two zeros. It is -53690 and 0. Also, notice that the highest date/time first integer is 2958463. In passing the second integer really has no bearing on my subject but I'll tell you that it stores the number of 3.33~ millisecond ticks since midnight for a given day.
Now run this:
Select
DateAdd(dd,53690,'1753-01-01 00:00:00.000');
And we get 1900-01-01 00:00:00.000. This is our reference point around which the first of the two internal integers is composed; it represent the arithmetic difference in days between 1900-01-01 00:00:00.000 and your other date/time value. This is why, internally, it can be negative or positive. This is also the source of another odd T-SQL quirk (like this is the first one:w00t:).
[Code]
Declare
@st DateTime,
@et DateTime;
Select
@st='1753-01-01 00:00:00.000',
@et='9999-12-31 23:59:59.998';
Select
DateDiff(yy,@st,@et);
[/code]
You get back 8246. No surprises here, this is just the number of years between our valid lowest and highest dates. Now run:
Declare
@st DateTime,
@et DateTime;
Select
@st='1753-01-01 00:00:00.000',
@et='9999-12-31 23:59:59.998';
Select
DateDiff(yy,0,@et-@st);
Huh:crazy:, the dreaded Arithmetic overflow error converting expression to data type datetime. But if you run:
Declare
@st DateTime,
@et DateTime;
Select
@st='1900-12-31 00:00:00.000',
@et='9999-12-31 23:59:59.998';
Select
DateDiff(yy,0,@et-@st);
Everything is ok, you get 8099, the number of years between those two date/time values.
So what happened here that we apparently lost 147 years of range (1900-1753) by using the DateDiff function in this manor? Well, the DateDiff function wants a DateTime value as it's second and third parameters so it can do the difference calculation with the knowledge of its internal days representation. When you pass 0 as the second parameter you are thwarting this process by calculating the difference yourself and possibly getting caught by the zero crossing problem!
Example:
1753-01-01 is internally represented by a days number of -53690 (the smallest valid days number)
1900-01-01 is internally represented by a days number of 0
9999-01-01 is internally represented by a days number of 2958463 (the largest valid days number)
Ex.1) If you take the arithmetic difference (using the binary minus operator) between 9999-01-01 and 1900-01-01 as days you'd get 2958099
Ex.2) If you take the arithmetic difference (using the binary minus operator) between 9999-01-01 and 1753-01-01 as days you'd get 3012153
Now, I'm going to tell you that this problem actually has nothing to do directly with the DateDiff function! When you take the arithmetic difference of two DateTime values the result is implicitly cast back into a DateTime value. Therefore, Ex.1 above would have no problem being passed into DateDiff because it will cast back into a DateTime data type. However, Ex.2 runs into the Arithmetic overflow error converting expression to data type datetime because 3012153 is larger than the highest internal days number!
So, we can see that DateDiff knows how to handle the difference calculation when one of its DateTime values has a negative representation, where an arithmetic difference doesn't! So, should we avoid using DateDiff in this manor? Of course not! Its solves some rounding problems for us when we do! Just use it with an awareness of what is going on under the covers!
Your 147 years have been returned!