In your original parameters Shift2Start seems to be after Shift2End. Or do you mean it should be the next day?

set @Shift2Start ='19:00'
set @Shift2End ='06:59'


I also tried to use datetimes instead of characters for comparision. However, I'm not sure the formula to convert datetime to time is correct.

>I'm not sure what you mean by "try the changes inside". I did get the query to run, but it is returning the wrong shift (falling into the else clause and returning zero) for the first record.
>
>
>

>Example data:
>Room    AnesStart                AnesEnd         ShiftNumber
>ROOM1   05/15/2007 03:05:00 05/15/2007 04:31:00     2
>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
>

>
>Query
>

>select
>  (Select substring(roomname,1,10) 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  when substring(convert(varchar(5),dbo.meetings.anesstart,114),1,5) between  @Shift1Start  and  @Shift1End then 1
>          when substring(convert(varchar(5),dbo.meetings.anesstart,114),1,5) between  @Shift2Start  and  @Shift2End then 2
>      else 0
>      end as ShiftNumber,
>