case statement in where clause - SQL Server

Question

I m trying to add a case or if statement in the where clause of my SQL query  I have a table of journey times with a start and end date  and a boolean field for each day to signify where the journey happens on that day  Here is what I have so far  but I m getting incorrect syntax errors   declare  date datetime set  Date    05 04 2012   declare  day nvarchar 50  set  day    Monday   Select   From Times WHERE  StartDate  lt    Date  AND  EndDate  gt    Date  CASE WHEN  day    Monday  THEN  AND  Monday   1  WHEN  day    Tuesday  THEN AND  Tuesday   1  ELSE AND  Wednesday   1   END

User · Answer

simply do the  select   Select   From Times WHERE  StartDate  lt    Date  AND  EndDate  gt    Date  AND    day    Monday  AND  Monday   1   OR   day    Tuesday  AND  Tuesday   1   OR  Wednesday   1

User · Answer

You don t need case in the where statement  just use parentheses and or   Select   From Times WHERE StartDate  lt    Date AND EndDate  gt    Date AND         day    Monday  AND Monday   1      OR   day    Tuesday  AND Tuesday   1      OR Wednesday   1     Additionally  your syntax is wrong for a case  It doesn t append things to the string--it returns a single value  You d want something like this  if you were actually going to use a case statement  which you shouldn t    Select   From Times WHERE  StartDate  lt    Date  AND  EndDate  gt    Date  AND 1   CASE WHEN  day    Monday  THEN Monday              WHEN  day    Tuesday  THEN Tuesday              ELSE Wednesday         END    And just for an extra umph  you can use the between operator for your date   where  Date between StartDate and EndDate   Making your final query   select        from      Times where      Date between StartDate and EndDate     and             day    Monday  and Monday   1          or   day    Tuesday  and Tuesday   1          or Wednesday   1

User · Answer

A CASE statement is an expression  just like a boolean comparison   That means the  AND  needs to go before the  CASE  statement  not within it    Select   From Times WHERE  StartDate  lt    Date  AND  EndDate  gt    Date   AND -- Added the  AND  here  CASE WHEN  day    Monday  THEN  Monday   1    -- Removed  AND       WHEN  day    Tuesday  THEN  Tuesday   1   -- Removed  AND       ELSE AND  Wednesday   1   END

[sql] case statement in where clause - SQL Server

Examples related to sql

Examples related to sql-server