Check intersection of two date ranges in SQL
Sometimes we have objects which have timespans in our database, such as valid dates for coupons, or say active dates of a sale, or trigger, or some other event. in searching through these, we may want to give a user the ability to see all of these objects which were 'active', or whose timespan intersects a user's search criteria. So the user gives a set of dates, and we want to see if any date inside the user's date at all also exists inside the date that the object in our DB was active. The following pseudo-code should hash out how I solved this problem.
AND(
(userselected_start_date <= dbobject_end_date AND userselected_end_date >= dbobject_start_date )
OR
(userselected_start_date <= dbobject_start_date AND userselected_end_date >= dbobject_end_date)
OR
((userselected_start_date <= dbobject_end_date AND userselected_start_date >= dbobject_start_date) OR
(userselected_end_date >= dbobject_start_date AND userselected_end_date <= dbobject_end_date))
)
These three OR statements essentially fill the three criteria about the user's timespan and how it relates to the timespan in the database. There are four possibilities for the set theory relationship between the date ranges.
- The date ranges share no common dates. This means the sets are disjoint, and in the above where clause, the rows would not be returned
- The date range the user selects is a superset of the database range
- The date range the user selects is a subset of the database range
- The date range the user selects has some members, but not all members, which are also found in the date range in our database
This is a bit obscure, but in writing web reports this has come up a few times. Hope it helps someone.
Update:
Ruud Campsteijn points out that it is much easier to check if dates don't intersect, and invert the results.
AND NOT (daterange1_startdate > daterange2_end_date OR daterange1_endate < daterange2_startdate)