datetime, between question

[hoggle]

I have a table set up with a startTime and endTime ie:
id startTime endTime
== ===================== ====================
1 1/15/2004 09:30:00 AM 1/15/2004 10:00:00 AM
2 1/15/2004 11:00:00 AM 1/15/2004 1:45:00 PM
3 1/15/2004 10:30:00 PM 1/15/2004 11:30:00 PM
4 1/15/2004 11:30:00 PM 1/16/2004 01:00:00 AM


now I need to be able to pass in another start and end time, and be able tell if that time range is within any of the other times for that day.

start end
so 1/15/2004 11:15:00 AM 1/15/2004 12:30 PM
would be true because it's between #2
1/15/2004 10:15:00 AM 1/15/2004 10:30:00 AM
would be false because it's not between any times

also I need it to check for day roll over ie:
1/16/2004 12:15:00 AM 1/16/2004 8:00:00 AM
would be true because it's between #4

hopefully this all makes sense

 

[mimi2]

try this:

declare @date datetime,
@in_between int

--set @date= getdate()
set @date = '1/15/2004 11:00:00 AM'

create table #i1( i int, j datetime, k datetime)
insert #i1 values(1 ,'1/15/2004 09:30:00 AM', '1/15/2004 10:00:00 AM')
insert #i1 values(2 , '1/15/2004 11:00:00 AM' , '1/15/2004 1:45:00 PM')

set @in_between = (select count(*) from #i1 where (i<= @date and j>= @date) )

select @date,j, k, (case when (@in_between > 0) then 'true'
else 'false'
end)
from #i1