>Hoping someone can help with this query. I'm trying to assign a shift number based off the begintime field of each returned record. I thought I could use the case statement, but I'm getting an syntax error on the first when statement near the keyword between.
>
>Thanks for any help or ideas.
>
>Kirk
>
>
>

>----------------------------------------------
>-- 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 @ltDate2

See fix inside your code.