Selecting overlapping date ranges on a date ranges

[andywyndom]

I am wondering if anyone wants to try and better this code sample. Here is the request: Given a range of dates (start, end) return all rows that cross that range where the rows contain a start and end date.

This appears to work. I just wonder if anyone else has had a similar question.

Thanks,

CREATE TABLE #tblDateRange (
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nchar](10) NOT NULL,
	[Start_Date] [datetime] NOT NULL,
	[End_Date] [datetime] NOT NULL
) ON [PRIMARY]

insert into #tblDateRange (Name, start_date, end_date) values    ('Andy      ' ,'Jan  1 2012 12:00AM', 'Jan 21 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values    ('Tom       ' ,'Jan  1 2012 12:00AM', 'Jan 31 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values    ('Frank     ' ,'Jan  1 2012 12:00AM', 'Jan 11 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values    ('Joe       ' ,'Jan  1 2012 12:00AM', 'Jan  3 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values    ('Hanna     ' ,'Jan  1 2012 12:00AM', 'Jan  6 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values    ('Michelle  ' ,'Aug  1 2012 12:00AM', 'Jan  8 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values    ('Meghan    ' ,'Jul  1 2012 12:00AM', 'Jan  9 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values    ('Peter     ' ,'Jun  1 2012 12:00AM', 'Jan 10 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values    ('Matthew   ' ,'May  1 2012 12:00AM', 'Jan 12 2012 12:00AM')
insert into #tblDateRange (Name, start_date, end_date) values    ('Ryan      ' ,'Apr  1 2012 12:00AM', 'Jan 16 2012 12:00AM')


DECLARE @start_date AS DATETIME
DECLARE @end_date AS DATETIME

SET @start_date = '1/17/2012'
SET @end_date = '1/22/2012'

select * from #tblDateRange

select * from #tblDateRange
WHERE (( start_date between  @start_date   and  @end_date and @end_date >= end_date) 
OR   ( start_date between  @start_date   and  @end_date) 
OR   ( end_date between  @start_date   and  @end_date ) 
OR   ( end_date between  @start_date   and  @end_date and @start_date<= start_date)   
OR   ( @start_date >= start_date AND  @start_date <= end_date)  
OR   ( @end_date  <= end_date  AND @end_date >= start_date))

[rch1231]

I see a couple of problems with the way you set up your data for the database. In several cases the end date is prior to the start date. The table assumes that the start and end date are not null and in many applications they can be, or at least the end date can be. Or the software should be checking when an end date is entered that it is greater than the start date or reject it.

Also when you set the variables for the dates to check against you need to add 00:00:00 (12:00AM) to the start date and 23:59:59 (11:59PM)to the end date (or add one day to the end date and 00:00:00 (12:00AM) and say less than).

[andywyndom]

I see a couple of problems with the way you set up your data for the database. In several cases the end date is prior to the start date. The table assumes that the start and end date are not null and in many applications they can be, or at least the end date can be. Or the software should be checking when an end date is entered that it is greater than the start date or reject it.

Also when you set the variables for the dates to check against you need to add 00:00:00 (12:00AM) to the start date and 23:59:59 (11:59PM)to the end date (or add one day to the end date and 00:00:00 (12:00AM) and say less than).

Thank you for the add a day advise.

Yeah, the data was sort of slapped together for the post, so I wasn't too careful with it. You are correct, most data of this sort would be trapped for a start_date less than an end_date.