hendridm
asked on
Query/SP to determine if date range overlaps record date ranges
I'm looking for a SELECT statement or stored procedure that allows me to return records whose date range overlaps a specified date range. Here is a sample table:
cid course_name date_start date_end description
==========================
1 Intro to Stuff 5/30/2006 7/2/2006 null
2 Ethics 1/2/2006 1/31/2006 null
3 Physics I 2/20/2006 7/1/2006 null
4 Adv. Algebra 2/23/2006 4/23/2006 null
5 Accounting 101 1/27/2006 6/6/2006 null
Let's say I give the user the following input:
Date Start: ____________
Date End: ____________
Let's say the user enters a start date of 1/25/2006 and an end date of 2/22/2006. I want to return all records that have dates have start/end dates that overlap the user input, 1/25/2006-2/22/2006, which in this example would be records 2, 3, and 5.
Does anyone understand what I'm looking for? Can you come up with a solution?
cid course_name date_start date_end description
==========================
1 Intro to Stuff 5/30/2006 7/2/2006 null
2 Ethics 1/2/2006 1/31/2006 null
3 Physics I 2/20/2006 7/1/2006 null
4 Adv. Algebra 2/23/2006 4/23/2006 null
5 Accounting 101 1/27/2006 6/6/2006 null
Let's say I give the user the following input:
Date Start: ____________
Date End: ____________
Let's say the user enters a start date of 1/25/2006 and an end date of 2/22/2006. I want to return all records that have dates have start/end dates that overlap the user input, 1/25/2006-2/22/2006, which in this example would be records 2, 3, and 5.
Does anyone understand what I'm looking for? Can you come up with a solution?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes......correct jmundsack i should have added that as well.
SELECT *
FROM tableName
WHERE NOT (@dateStart > date_end OR @dateEnd < date_start)
That is, if the desired start date is after the table end date or the desired end date is before the table start start date, then the row should NOT be selected; otherwise it should be.
FROM tableName
WHERE NOT (@dateStart > date_end OR @dateEnd < date_start)
That is, if the desired start date is after the table end date or the desired end date is before the table start start date, then the row should NOT be selected; otherwise it should be.