4

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>)
  • Seems you can convert 0 to DATETIME but not to DATETIME2 (not directly at least). – ypercubeᵀᴹ Jun 4 '18 at 21:28
  • 1
    So DATEDIFF(), if one of the 2 last arguments is of an accepted type (date, datetime, datetime2, time, smalldatetime), does an implicit conversion of the other argument. Converting 0 to DATETIME2, fails. – ypercubeᵀᴹ Jun 4 '18 at 21:31
  • 1
    Seems I was not entirely correct. it's the other conversion that fails (datetime2 to datetime). Hail Martin ;) – ypercubeᵀᴹ Jun 5 '18 at 7:57
7

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.