How can I determine in SQL Server if the dateTime range overlaps another

Question

We have, for example. following table

| ID | startDateTime | endDateTime | +----+---------------------+---------------------| | 1 | 2010-01-01 10:30:00 | 2010-01-01 11:00:00 | | 2 | 2010-01-01 10:30:00 | 2010-01-01 11:30:00 | | 3 | 2010-01-01 11:00:00 | 2010-01-01 11:30:00 | | 4 | 2010-01-01 11:00:00 | 2010-01-01 12:00:00 | | 5 | 2010-01-01 11:30:00 | 2010-01-01 12:00:00 | | 6 | 2010-01-01 10:30:00 | 2010-01-01 12:00:00 | 

I, for example. request using

 @startDateTime 2010-01-01 11:00:00 @endDateTime 2010-01-01 11:30:00 

I want to get the following identifiers: 2, 3, 4, 6

My brain is a confusion for making the right statement ... :(

Answer 1

You can do it as follows:

 declare @T table (ID int, startDateTime datetime, endDateTime datetime) insert into @T values ( 1 , '2010-01-01 10:30:00' , '2010-01-01 11:00:00'), ( 2 , '2010-01-01 10:30:00' , '2010-01-01 11:30:00'), ( 3 , '2010-01-01 11:00:00' , '2010-01-01 11:30:00'), ( 4 , '2010-01-01 11:00:00' , '2010-01-01 12:00:00'), ( 5 , '2010-01-01 11:30:00' , '2010-01-01 12:00:00') declare @startDateTime datetime declare @endDateTime datetime set @startDateTime = '2010-01-01 11:00:00' set @endDateTime = '2010-01-01 11:30:00' select * from @T where startDateTime < @endDateTime and endDateTime > @startDateTime