You don't need case in the where statement, just use parentheses and or:
Select * From Times
WHERE StartDate <= @Date AND EndDate >= @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 <= @Date) AND (EndDate >= @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
)