Conditional where clause SQL Server 2012

Question

I've searched around, but haven't found the similar situation to what I'm trying to achieve. I want to execute the where clause expression only when the condition is true, otherwise I want to ignore the data that doesn't match it.

Where (
        case when LeaveDateTime > FromDate and EnteringDateTime < ToDate then
        (dateadd(d, Threshhold, LeaveDateTime) >= EnteringDateTime
        end
);

I get Incorrect syntax near '>=' Can someone please help me solve this?

For an example of using `CASE` in an `JOIN` `ON` clause to check multiple conditions see [this](http://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition). It works the same way in a `WHERE` clause. — HABO, Jan 09 '15 at 02:31

score 5 · Answer 1 · answered Jan 08 '15 at 23:09

Replace this with simple logic:

Where (not (LeaveDateTime > FromDate and EnteringDateTime < ToDate) or
       dateadd(day, Threshhold, LeaveDateTime) >= EnteringDateTime
      );

If the variables take on NULL values, then the logic is somewhat more cumbersome.

Your version doesn't work because case is a scalar expression. A boolean comparison is not a scalar expression, so it cannot be the value returned by an then.

score 1 · Answer 2 · answered Jan 08 '15 at 23:16

If I was trying to filter our a table using a condition in the where clause using your example you would probably have to alter it to something like;

WHERE 1 = (CASE
    WHEN when LeaveDateTime > FromDate and EnteringDateTime < ToDate
       THEN 1
    ELSE 0 END)

if I am understanding your question correctly :)

score 1 · Accepted Answer · answered Jan 08 '15 at 23:25

"execute where clause expression only when the condition is true, otherwise I want to ignore the data that doesn't match it"

Since you say you want to ignore, i.e. not include, data otherwise if the first condition is not met, then it sounds like you want a simple AND. That's may not be what you intended to express, which is why it's often important to provide example results to clarify.

Where 
( 
   (LeaveDateTime > FromDate and EnteringDateTime < ToDate) 
      AND
   dateadd(day, Threshhold, LeaveDateTime) >= EnteringDateTime
);

Conditional where clause SQL Server 2012

3 Answers3