I'm trying to include a numeric value for ShiftNumber using the case statement inside the query. It's not working...and I don't know if this is possible. The shifts (would be passed as parameters but hard coded here) are the times for the shifts. The value I'm checking them against is in a datetime field.

Example data:
Room AnesStart AnesEnd ShiftNumber
ROOM1 05/15/2007 15:35:00 05/15/2007 16:35:00 1
ROOM1 05/15/2007 19:01:00 05/15/2007 22:35:00 2
ROOM2 05/16/2007 06:30:00 05/16/2007 07:45:00 2

Any help greatly appreciated.

Thanks

declare 
@BeginTime datetime,
@EndTime datetime,
@deptID int,
@Shift1Start varchar(5),
@Shift1End   varchar(5),
@Shift2Start varchar(5),
@Shift2End   varchar(5)

set @Begintime = '05/01/2007 00:00:01'
set @EndTime   = '05/31/2007 23:59:59'
set @DeptID=35
set @Shift1Start ='07:00'
set @Shift1End   ='18:59'
set @Shift2Start ='19:00'
set @Shift2End   ='06:59'


select 
  (Select roomname from coRooms where coRooms.RoomID=meetings.RoomID) as RoomDesc,
  datediff(mi,dbo.meetings.anesstart,dbo.meetings.anesend) as AnesMins,
  dbo.meetings.AnesStart,
  dbo.meetings.AnesEnd,
  case substring(convert(varchar(5),dbo.meetings.anesstart,114),1,5) 
      when between @Shift1Start and @Shift1End then 1
      when between @shift2Start and @Shift2End then 2
      else 0
      end as ShiftNumber
  
      
from dbo.meetings
where dbo.meetings.iscompleted=1
and   dbo.meetings.deptID=@DeptID
and   dbo.meetings.Begintime between @BeginTime and @EndTime
order by RoomDesc