Try with the changes inside: (the idea is to compare datetime values and not the characters) - not tested.

>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 datetime,
>@Shift1End  datetime,
>@Shift2Start datetime,
>@Shift2End   datetime
>
>set @Begintime = '05/01/2007 00:00:01'
>set @EndTime   = '05/31/2007 23:59:59'
>set @DeptID=35
>set @Shift1Start ='01/01/1900 07:00'
>set @Shift1End   ='01/01/1900 18:59'
>set @Shift2Start ='01/01/1900 19:00' //?
>set @Shift2End   ='01/01/1900 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 cast(dbo.meetings.anesstart - cast(dbo.meetings.anesstart as Integer) as DateTime)
>      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
>