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>)