Try (not tested)

WHERE 
	CONVERT(char(8), det.EnterOR, 8) BETWEEN @tcStartTime AND '23:59:59' 
	CONVERT(char(8), det.EnterOR, 8) BETWEEN '00:00:00' AND @tcEndTime  
	CONVERT(char(8), det.ExitOR, 8) BETWEEN @tcStartTime AND '23:59:59' 
	CONVERT(char(8), det.ExitOR, 8) BETWEEN '00:00:00' AND @tcEndTime  

>The purpose of the query below is return all cases that were done during a given time frame, but limit it to cases that have and EnterOR or ExitOR time between a set of user defined hours (such as 1500 and 0700 the next day). That is the part I'm having trouble with. The code below works with just the hours of the enterOR/ExitOR time. But if the user passes a value such as 1530 and 0630 then my logic get cases they don't want. I'm not sure how to build a where clause that can cast a value based on the case date or next day.
>
>Thanks for any help and pointers.
>
>Kirk
>
>

>Declare
>@QueryFrom DateTime,
>@QueryTo DateTime,
>@tcStartTime varchar(8),
>@tcEndTime varchar(8)
>
>set @QueryFrom = '12/01/2004 00:00:01'
>set @QueryTo   = '12/31/2004 23:59:59'
>set @tcStartTime = '14:59:00'
>set @tcEndTime   = '06:59:00'
>
>select det.meetingnumber as ConfNumber,det.begintime,det.EndTime,det.EnterOR,det.ExitOR,
>	(Select rtrim(Lastname)+', '+rtrim(FirstName) from dbo.patients where patientID=det.PatientID) as PatientName,
>	(Select mpinumber from dbo.patients where patientID=det.PatientID) as medRecNo,
>	(SELECT TOP 1 RTRIM(dbo.coper.lastname) + ', ' + RTRIM(dbo.coper.firstname)
>	   FROM dbo.meetproc INNER JOIN
>                dbo.coper ON dbo.meetproc.coperid = dbo.coper.coperid
>                where dbo.meetproc.meetingnumber=det.meetingnumber
>	   ORDER BY dbo.meetproc.sysorder) as SurgeonName,
>	dbo.Get_ProceduresOnCase(det.MeetingNumber) as PrimaryProc,
>
>	(Select deptdesc from dbo.coDept where coDept.DeptID=det.DeptID) as DepartmentDesc,
>	det.deptid,
>	substring(@tcStartTime,1,2),
>	substring(@tcEndTime,1,2),
>	datepart(hh,det.EnterOR) as DatePartEnterOR,
>	DatePart(hh,det.ExitOR) as DateParExitOR
>
>from
>(Select meetings.meetingnumber,BeginTime,EndTime,Enteror,ExitOR,PatientID,DeptID
>from dbo.meetings
>where begintime between @QueryFrom and @QueryTo
>and iscompleted=1) as det
>where (datepart(hh,det.EnterOR) between substring(@tcStartTime,1,2) and 23
>   or datepart(hh,det.EnterOR) between 0 and substring(@tcEndTime,1,2)-1)
>   or (datepart(hh,det.ExitOR)  between substring(@tcStartTime,1,2) and 23
>   or datepart(hh,det.ExitOR) between 0 and substring(@tcEndTime,1,2)-1)
>order by departmentdesc,EnterOR
>