How to use ‘between’ in CASE statement?

I need to select Shift from my production table such that if the time time is between 05:00 PM to 06:30 AM it is Shift B else its Shift A. The SQL that I have written is quite simple but does not gives me the expected results. The SQL is

declare @time varchar(50)
set @time= (select time = convert(varchar,getdate(),108))

select Shift_FK_ID from production
where op_Date='2021-08-23' and Shift_FK_ID=
(CASE WHEN @time between '17:00:00' and  '06:30:00' 
Then '2'
Else '1'
End)

The Result I get is

Shift_FK_ID
1
1
1

Where op_Date is operations date. The code was simple but can’t figure it out. Maybe it has something to do with 24 hrs format.

Help required.

Regards

Answer

I think you want logic like this:

select (case when convert(time, getdate()) between '06:30:00' and '17:00:00'
             then 1 else 2
        end) as shift

There is no need to use strings for these comparisons.

Advertisement

Answer