Strange behaviour in CASE expression using dateadd

Question

I'm expiriencing some strange behaviour in a SQL statement which is using a CASE. It is reproducable with the following example:

SELECT
    CASE
    WHEN 1=1
        THEN 'foo'
    WHEN 1=2
        THEN (DATEADD(s, 1435586700, '01/01/1970 00:00:00'))
    WHEN 1=3
        THEN (DATEADD(s, 1435586700, '01/01/1970 00:00:00'))
    ELSE
        'bar'
    END AS result

If you execute this on an SQL Server 2008 it returns no result. As soon as you change it for the second or third case to succeed, it returns a value. If you remove those cases completely, it works just fine.

At first I thought it was a problem with DATEADD being executed in any case and failing because of passed parameter values. But in my example they are constant and should work in any case.

What is going on here?

Answer 1

case is an expression whose type is determined at compile-time. However, the values are not actually evaluated until run-time.

By the rules of type-conversion, I think that the expression will return a datetime. That results in an error for the 'foo' and 'bar' values -- when the when conditions are met.