Why do subsequent unmet conditions in my CASE statement affect my return data type? 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 &quot;I&quot; 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 &quot;1900-01-09 00:00:00.000&quot;
I would think &quot;D&quot; shouldn't get evaluated at all because, in SQL Server, once a condition evaluates to true (&quot;I&quot; in this case), it should break out of the CASE statement--so I'm not sure why &quot;D&quot;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 &quot;I&quot;:
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?
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).