Why do subsequent unmet conditions in my CASE statement affect my return data type?

Question

I have the following variables and CASE statement:

DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'

SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/2021', @DATE_VALUE)
       END AS ConvertedValue

There's nothing special about it. It returns 8 (int) as I expect it to.

However, as soon as I add to my CASE statement a conditional that returns a datetime data type, I no longer get 8 (int), despite continuing to use "I" as the @CONVERT_CODE value.

DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'

SELECT CASE @CONVERT_CODE WHEN 'I' THEN DATEDIFF(day, '04/21/2021', @DATE_VALUE)
                          WHEN 'D' THEN GETDATE()
       END AS ConvertedValue

Instead, I get the datetime value of "1900-01-09 00:00:00.000"

I would think "D" shouldn't get evaluated at all because, in SQL Server, once a condition evaluates to true ("I" in this case), it should break out of the CASE statement--so I'm not sure why "D"s data type is affecting the overall return data type. Also, it doesn't matter if I reverse the order of the conditions and still use "I":

DECLARE @DATE_VALUE VARCHAR(20) = '4/29/2021'
DECLARE @CONVERT_CODE VARCHAR(1) = 'I'

SELECT CASE @CONVERT_CODE WHEN 'D' THEN GETDATE()
                          WHEN 'I' THEN DATEDIFF(day, '04/21/2021', @DATE_VALUE)
       END AS ConvertedValue

Am I missing something?

Answer 1

The column defined by your CASE expression has a data type, as all columns do.

SQL Server determines the resulting type in its usual way, making use of data type precedence. The type datetime has a higher precedence than integer.

You might be expecting the CASE expression to return different types on different paths, but that is not possible (sql_variant aside).

Answer 2

"1900-01-09 00:00:00.000" is the date number 8. The problem you are facing rely on the fact that case will return only one data type. It will evaluate every case expression, choosing a data type and implicit converting every results to that data type.

In your case one case returns int (datediff) and another one datetime so sql decided to use datetime and then converted 8 to datetime that is what you have found.

This data type decision is evaluated before applying the case statement to each row selected.

Answer 3

I would think "D" shouldn't get evaluated at all because, in SQL Server, once a condition evaluates to true ("I" in this case), it should break out of the CASE statement

Too many shoulds - try casting an eye over Dirty Secrets of the CASE Expression by Aaron Bertrand.