between times in a date time field

[roody91]

How do i program a column to show a shift based on the time value od a date field ie if the time portion(HH:MM) of the date time field is between 07:30 and 16:30 then 'Day shift' if between 16:30 and 23:30 then 'Eveneing Shift' etc.

Thanks,

FB

 

[Bygbobbo]

This should get you started:


declare @r datetime
--set @r = '2003-06-19 17:25:36.163'
--set @r = '2003-06-19 14:25:36.163'
set @r = '2003-06-19 07:25:36.163'

select
case
when (datepart(hh,@r) <= 16 and datepart(mm,@r) <= 30 and datepart(hh,@r) >= 7 and datepart(mm,@r) >= 30) then 'Day Shift'
else 'Evening Shift'
end

you can substitute the @r variable with your datetimefield, and include more criteria if you have more than one category.

Hope this helps,
Bygs