AFAICS, it's shift 2 if it isn't shift 1

  case substring(convert(varchar(5),dbo.meetings.anesstart,114),1,5) 
      when between @Shift1Start and @Shift1End then 1
      else 2
      end as ShiftNumber

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