What does 0 in DATEDIFF(MINUTE, 0, <Date>) actually mean?

Question

So, our data team asked for some help in solving a problem they had. I eventually tracked it down to some really out of range data (1/1/0001) and a DATEDIFF function they were using. While I've solved their problem, It came about that I don't actually know what the 0 turns into when used as they were using it.

I originally thought it was closer to an integer overflow rather than a true conversion error, but that's not it. I tried it on a SQL 2016 box with DATEDIFF_BIG and same error. I have a sample for you guys below to play with along with what works and what doesn't.

/** Setup The Sample */
DECLARE @TestValue DATETIME2(7)
SET @TestValue = '0001-01-01 10:30:00.0000000'

/** Conversion Error 
Msg 242, Level 16, State 3, Line 10
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.
*/
SELECT DATEDIFF(MINUTE, 0, @TestValue)

--Also does not work, same error.
SELECT DATEDIFF_BIG(MINUTE, 0, @TestValue)

/** Works */
SELECT DATEDIFF(MINUTE, '1/1/1900', @TestValue)

/** Works */
SELECT DATEDIFF(MINUTE, CAST(0 AS DATETIME), @TestValue)

/** Doesn't Work, you can't cast 0 to a DATETIME2 */
--SELECT DATEDIFF(MINUTE, CAST(0 AS DATETIME2), @TestValue)

/** Works (or no error, which is fine)*/
SELECT DATEDIFF(MINUTE, 0, TRY_CAST(@TestValue AS DATETIME))

Bonus Question, since 0 doesn't work in all cases for DATETIME2, what's the alternative?

WHAT WE DECIDED TO DO

So, I have started recommending my team do the following, since you see 0 in lots of examples for datemath (first day of month, etc.). So I recommend that you do an explicit cast of 0 to datetime, then continue as you will. This will avoid the error while still working. So:

DATEDIFF(MINUTE, CAST(DATETIME, 0), <Date>)

Answer 1

You can see what's going on by adding the expression to a query with a FROM clause and looking at the compute scalar.

This shows the following.

+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+
|                          Expression                           |                                                       Evaluated As                                                        |
+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+
| DATEDIFF(MINUTE, 0, @TestValue)                               | Scalar Operator(datediff(minute,'1900-01-01 00:00:00.000',CONVERT_IMPLICIT(datetime,[@TestValue],0)))                     |
| DATEDIFF_BIG(MINUTE, 0, @TestValue)                           | Scalar Operator(datediff_big(minute,'1900-01-01 00:00:00.000',CONVERT_IMPLICIT(datetime,[@TestValue],0)))                 |
| DATEDIFF(MINUTE, '1/1/1900', @TestValue)                      | Scalar Operator(datediff(minute,'1900-01-01 00:00:00.0000000 +00:00',CONVERT_IMPLICIT(datetimeoffset(7),[@TestValue],0))) |
| DATEDIFF(MINUTE, CAST(0 AS DATETIME), @TestValue)             | Scalar Operator(datediff(minute,'1900-01-01 00:00:00.000 +00:00',CONVERT_IMPLICIT(datetimeoffset(7),[@TestValue],0)))     |
| DATEDIFF(MINUTE, CAST('1900-01-01' AS DATETIME2), @TestValue) | Scalar Operator(datediff(minute,'1900-01-01 00:00:00.0000000 +00:00',CONVERT_IMPLICIT(datetimeoffset(7),[@TestValue],0))) |
| DATEDIFF(MINUTE, 0, TRY_CAST(@TestValue AS DATETIME))         | Scalar Operator(datediff(minute,'1900-01-01 00:00:00.000',TRY_CAST([@TestValue] AS datetime)))                            |
+---------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------+

If you pass a literal 0 to this function it will always be implicitly cast to datetime.

Casting an int to datetime returns 1900-01-01 + <int> days so 1900-01-01.

The issue for you is what the datatype of the third parameter gets cast to. @TestValue is of datetime2 - when you pass an integer both sides get implicitly cast to datetime.

'0001-01-01 10:30:00.0000000' is out of range for datetime hence the error.

In the times where it succeeds the parameters both get cast to datetimeoffset(7)