[SOLVED] Return condition from CASE expression in where clause (error: incorrect syntax near '<'.)

Return condition from CASE expression in where clause (error: incorrect syntax near '<'.)

In T-SQL, I get this error

Incorrect syntax near '<'.

This is my T-SQL statement:

SELECT *
FROM TableName
WHERE CStatus = 'Active'
  AND ServiceStatus = 'Active'
  AND DATEDIFF(day, OnDate, OffDate) > 0
  AND 
     CASE 
         WHEN EOMONTH(CA.OnDate) = 31 
             THEN DATEDIFF(day, OnDate, OffDate) <= 31 
         ELSE DATEDIFF(day, OnDate, OffDate) <= 30 
     END

I am trying to use a CASE expression in my WHERE clause, but I am getting that error.

I want compare if OnDate month have 31 days then 31 days otherwise 30 days.

Solution

A case expression returns a value, not a snippet of a condition, hence the syntax error.

One approach is to rewrite the condition so the datediff part remains the same, and only the value its compared to is calculated by a case expression:

SELECT *
FROM   TableName
WHERE  CStatus = 'Active'
  AND  ServiceStatus = 'Active'
  AND  DATEDIFF(day, OnDate, OffDate) > 0
       DATEDIFF(day, OnDate, OffDate) < CASE EOMONTH(CA.OnDate) 
                                             WHEN 31 THEN 31
                                             ELSE 30
                                        END