>---------------------------------------------- >-- Parameters >---------------------------------------------- >declare @ltDate1 varchar(10) ='12/01/2009 00:00:00' >declare @ltDate2 varchar(10) ='12/15/2009 23:59:59' >declare @s1Start varchar(5) = '06:00' >declare @s1End varchar(5) = '10:00' >declare @s2Start varchar(5) = '10:00' >declare @s2End varchar(5) = '14:00' >declare @s3Start varchar(5) = '14:00' >declare @s4Ends varchar(5) = '18:00' >----------------------------------------------- >-- Main Query ----------------------------------------------- select meetingnumber,begintime,endtime, ShiftNumber = Case when CONVERT(varchar(5),Begintime,114) between @s1Start and @s1End then 1 when CONVERT(varchar(5),Begintime,114) between @s2Start and @s2End then 2 when CONVERT(varchar(5),Begintime,114) between @s3Start and @s3End then 3 End from dbo.meetings where begintime between @ltDate1 and @ltDate2See fix inside your code.