SQL CASE in Query - Odd behavior

Question

Using a Case Statement in a query seems to work fine as long as the only case you want to match to is a string. But if you want to put ">" greater than (date or number), etc, SSMS automatically adds apostrophes around your statement (below) as if it were a string.

How can I get it to accept "greater than date" or "greater than number", etc??

Answer 1

There are two types of CASE expression:

• The simple CASE expression compares an expression to a set of simple expressions to determine the result.
• The searched CASE expression evaluates a set of Boolean expressions to determine the result.

You want a searched CASE expression. This means that the WHEN should come immediately after the CASE keyword.

CASE WHEN SoldDate <= SubjectDate THEN ...

Answer 2

There are two types of CASE expressions (from msdn):

A Simple CASE compares the specified expression for EQUIVALENCY ONLY

SELECT FieldName CASE WHEN 'Blah' THEN 'Foo' ELSE 'Bah' END

A Searched CASE can do inequalities too but has more verbose syntax:

SELECT CASE WHEN FieldName >= 'Blah' THEN 'Foo' ELSE 'Bah' END

You are trying to use the Simple syntax and need to use the Searched syntax by explicitly writing out the fieldname and comparison for each evaluation.