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

回答1:

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)

回答2:

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

回答3:

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))