Using a CASE in a WHERE clause with BETWEEN

Question

I'm trying to write a where clause with a case statement that will look at @TimeType and then only return the rows that are between those time ranges. So basically I have 3 times (morning, afternoon, and evening). I'm getting a syntax error on using between. The start_time field is sql time data type. Here's what I have so far:

declare @TimeType int

select * from events
where
start_time = case 
when @TimeType = 0 then start_time between '00:00:00' and '11:59:00' 
when @TimeType = 1 then start_time between '12:00:00' and '16:59:00' 
when @TimeType = 2 then start_time between '17:00:00' and '23:59:00' 
end

score 0 · Answer 1 · answered Jan 09 '19 at 15:10

Just use regular logic:

select e.* 
from events e
where (@TimeType = 0 and start_time between '00:00:00' and '11:59:00') or
      (@TimeType = 1 then start_time between '12:00:00' and '16:59:00') or 
      (@TimeType = 2 then start_time between '17:00:00' and '23:59:00');

In generally, using case expressions in where clauses is discouraged, because it impedes the optimizer . . . and it can be harder to follow. Usually, the same logic can be expressed using basic boolean logic.

score 0 · Answer 2 · answered Jan 09 '19 at 15:11

You can use Boolean logic instead in WHERE clause :

WHERE (@TimeType = 0 AND start_time between '00:00:00' and '11:59:00') OR
      (@TimeType = 1 AND start_time between '12:00:00' and '16:59:00') OR
      (@TimeType = 2 AND start_time between '17:00:00' and '23:59:00')

Using a CASE in a WHERE clause with BETWEEN

2 Answers2