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>)
Best Answer
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.
If you pass a literal
0
to this function it will always be implicitly cast todatetime
.Casting an
int
todatetime
returns1900-01-01 + <int> days
so1900-01-01
.The issue for you is what the datatype of the third parameter gets cast to.
@TestValue
is ofdatetime2
- when you pass an integer both sides get implicitly cast todatetime
.'0001-01-01 10:30:00.0000000'
is out of range fordatetime
hence the error.In the times where it succeeds the parameters both get cast to
datetimeoffset(7)