CODEWITHSUNDEEP
sqlsql-server
user10003750
user10003750

Reputation: 51

How to check condition in then block using SQL Server?

I'm trying to check 

SELECT 
    *
FROM
    table 
WHERE
    CASE
       WHEN startdate IS NULL 
          THEN updatedon >= enddate 
          ELSE updatedon BETWEEN startdate AND enddate

I'm getting error in THEN block in the condition ">".

Upvotes: 1

Views: 144

Answers (4)

Majid Akbari
Majid Akbari

Reputation: 210

You cannot have a condition as a result for CASE, try this:

SELECT *
FROM _table
WHERE startdate IS NULL
      AND updatedon >= enddate
      OR startdate IS NOT NULL
      AND updatedon BETWEEN startdate AND enddate;

Upvotes: 1

anatol
anatol

Reputation: 1760

In the code below, if startdate isn't NULL then updatedon BETWEEN startdate AND enddate will be used. Else - updatedon BETWEEN enddate AND '31-12-9999', where '31-12-9999' is equivalent of a date type max value, so, it's equal to updatedon >= enddate.

SELECT *
FROM _table
WHERE updatedon BETWEEN ISNULL(startdate, enddate) AND CASE
                                                           WHEN startdate IS NULL
                                                           THEN DATEFROMPARTS(9999, 12, 31)
                                                           ELSE enddate
                                                       END;

Upvotes: 0

LukStorms
LukStorms

Reputation: 29667

Similar to the other answers, this would give the expected result:

SELECT * 
FROM table 
WHERE (
   (startdate IS NOT NULL AND updatedon BETWEEN startdate AND enddate) 
   OR (startdate IS NULL AND updatedon >= enddate)
);

But if you still would like to using a CASE WHEN, then it could look like this:

SELECT * 
FROM table 
WHERE 
  (CASE 
   WHEN startdate IS NOT NULL AND updatedon BETWEEN startdate AND enddate THEN 1
   WHEN startdate IS NULL AND updatedon >= enddate THEN 2
   ELSE 0
   END) > 0

Note that the CASE WHEN returns a value.

But that would just over-complicate the SQL. And probably be less performant.
Since it'll be harder for the query optimizer to find the fastest execution plan.

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use Boolean logic instead of case expression :

select *
from table t
where (startdate is null and updatedon >= enddate) or
      (startdate is not null and (updatedon >= startdate and updatedon <= enddate)
      );

Upvotes: 3

Related Questions