CODEWITHSUNDEEP
sqlselectswitch-statementcase
CipherIS
CipherIS

Reputation: 93

Add Case Statement in Where Clause

I need to add a case statement in a where clause. I want it to run either statement below depending on the value of TermDate.

Select * 
from myTable
where id = 12345
    AND TermDate CASE  
    WHEN NULL THEN
       AND getdate() BETWEEN StartDate AND DATEADD(dd, 30, StartDate)
    ELSE
    AND GETDATE < TermDate
    END

Upvotes: 3

Views: 51121

Answers (3)

user2989408
user2989408

Reputation: 3137

You can accomplish this using ANDs and ORs. Try the following query. 

Select * 
From myTable
where id = 12345
AND ((TermDate IS NULL 
          AND GETDATE() BETWEEN StartDate AND DATEADD(dd, 30, StartDate)) 
    OR (GETDATE() < TermDate))

Upvotes: 2

Patrick Hofman
Patrick Hofman

Reputation: 157098

Since we all posted three exact answers, obviously too much, here a version that uses your case when construction.

use this:

select * 
from myTable
where id = 12345
AND   case
      when TermDate IS NULL
           AND getdate() BETWEEN StartDate AND DATEADD(dd, 30, StartDate)
      then 1
      when GETDATE < TermDate
      then 1
      else 0
      end
      = 1

Upvotes: 2

Mureinik
Mureinik

Reputation: 311998

Why not just use an OR condition?

SELECT * 
FROM  myTable
WHEN  id = 12345
AND   ((TermDate IS NULL AND 
        getdate() BETWEEN StartDate AND DATEADD(dd, 30, StartDate)) OR
       GETDATE() < TermDate)

Upvotes: 8

Related Questions