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 <= @Date) AND (EndDate >= @Date)
CASE WHEN @day = 'Monday' THEN
AND (Monday = 1)
WHEN @day = 'Tuesday' THEN
AND (Tuesday = 1)
ELSE
AND (Wednesday = 1)
END
This question is related to
sql
sql-server
simply do the select
:
Select * From Times
WHERE (StartDate <= @Date) AND (EndDate >= @Date) AND
((@day = 'Monday' AND (Monday = 1))
OR (@day = 'Tuesday' AND (Tuesday = 1))
OR (Wednesday = 1))
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 <= @Date) AND (EndDate >= @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
Source: Stackoverflow.com