Date Conflicts, Intersections and Co.

Often you will have to deal with Dates and Ranges. Lets have a look at a tricky case that often occurs when looking for date intersections ( conflicting dates ). The best and fastest way to solve this is actually not to test if a date (range) is inside the allowed date intevall, but to test if it is not outside the intervall.

Here is sample of intersection tests (green) on date ranges.

As you can see, the green ones are intersections the red ones are not. So it the easiest way to this as a Select statement would be:

SELECT * FROM t1 WHERE NOT ( data.start_date  > start_date OR data.end_date < end_date )

which could also be expressed as:

SELECT * FROM t1 WHERE  data.end_date >= start_date AND data.start_date <= end_date