Date range overlap detection in SQL

Introduction

We use date range overlap detection to find if two time periods share any common days. This helps avoid scheduling conflicts or double bookings.

Checking if two hotel room bookings overlap to prevent double booking.

Finding if employee vacation dates clash with project deadlines.

Detecting overlapping promotions or discounts in a store.

Verifying if rental car reservations overlap for the same vehicle.

Syntax

SQL

SELECT * FROM table1 t1
JOIN table2 t2 ON t1.start_date <= t2.end_date AND t1.end_date >= t2.start_date;

This syntax checks if the start of one range is before or on the end of the other, and vice versa.

It works for inclusive date ranges, meaning the start and end dates count as part of the range.

Examples

Finds all pairs of bookings that overlap, excluding comparing a booking with itself.

SQL

SELECT * FROM bookings b1
JOIN bookings b2 ON b1.start_date <= b2.end_date AND b1.end_date >= b2.start_date
WHERE b1.id != b2.id;

Detects overlapping events by comparing their start and finish dates.

SQL

SELECT * FROM events e1
JOIN events e2 ON e1.start <= e2.finish AND e1.finish >= e2.start
WHERE e1.event_id <> e2.event_id;

Sample Program

This query finds pairs of reservations that overlap by checking if their date ranges share any days. It excludes pairs where the same reservation is compared to itself and avoids duplicate pairs by using r1.id < r2.id.

SQL

CREATE TABLE reservations (
  id INT,
  start_date DATE,
  end_date DATE
);

INSERT INTO reservations VALUES
(1, '2024-06-01', '2024-06-05'),
(2, '2024-06-04', '2024-06-10'),
(3, '2024-06-11', '2024-06-15');

SELECT r1.id AS res1, r2.id AS res2
FROM reservations r1
JOIN reservations r2 ON r1.start_date <= r2.end_date AND r1.end_date >= r2.start_date
WHERE r1.id < r2.id;

Important Notes

Make sure your date columns are stored as DATE or DATETIME types for accurate comparison.

Using '<=' and '>=' includes the boundary dates as overlapping.

To avoid duplicate pairs, use conditions like r1.id < r2.id or r1.id != r2.id carefully.

Summary

Date range overlap detection helps find if two time periods share any days.

Use conditions comparing start and end dates with <= and >= operators.

This technique is useful for scheduling, booking, and event planning.