SQL Query to Find Overlapping (Conflicting) Date Ranges

Date ranges are often stored in a database table as a pair of datetime columns; for example, start_date and end_date. A common operation on date ranges is to find those that lie outside or overlap a given date range. A hotel reservation system for example might need to check if a room is available from Jun/1/2012 5:00 PM to Jun/3/2012 11:00 AM before confirming. Writing a query for this is much easier than you expect.

Date Range Query for Exclusive End Dates

-- 1.1) select date ranges that lie outside [d1, d2) (d2 and end_date are exclusive)
SELECT * FROM <table> WHERE @d2 <= start_date OR  end_date <= @d1

-- 1.2) select date ranges that overlap [d1, d2) (d2 and end_date are exclusive)
SELECT * FROM <table> WHERE @d2 >  start_date AND end_date >  @d1

Date Range Query for Inclusive End Dates

-- 2.1) select date ranges that lie outside [d1, d2] (d2 and end_date are inclusive)
SELECT * FROM <table> WHERE @d2 <  start_date OR  end_date <  @d1

-- 2.2) select date ranges that overlap [d1, d2] (d2 and end_date are inclusive)
SELECT * FROM <table> WHERE @d2 >= start_date AND end_date >= @d1

The above queries are based on Allen's Interval Algebra. The following ASCII art should explain the first query:

       @d1 |--------| @d2       
           .        . |--------| @d2 < start_date
           .        |--------|   @d2 = start_date
|--------| .        .            end_date < @d1
  |--------|        .            end_date = @d1
    |--------|      .            
           .      |--------|     
           |--------|            
           . |----| .            
         |------------|          

Example Queries

The following SQL script should help you test the queries and their results:

1) Example Data and Query for Exclusive End Dates

CREATE TABLE table1 (
  start_datetime DATETIME NOT NULL,
  end_datetime DATETIME NOT NULL,
  comments VARCHAR(100) NOT NULL,
  KEY (start_datetime),
  KEY (end_datetime)
);

INSERT INTO table1 (start_datetime, end_datetime, comments) VALUES
('2012-05-01 00:00:00', '2012-06-01 00:00:00', 'May 01 2012 - May 31 2012'),
('2012-05-01 00:00:00', '2012-06-16 00:00:00', 'May 01 2012 - Jun 15 2012'),
('2012-05-01 00:00:00', '2012-08-01 00:00:00', 'May 01 2012 - Jul 31 2012'),
('2012-06-10 00:00:00', '2012-06-20 00:00:00', 'Jun 10 2012 - Jun 19 2012'),
('2012-06-16 00:00:00', '2012-08-01 00:00:00', 'Jun 16 2012 - Jul 31 2012'),
('2012-07-01 00:00:00', '2012-08-01 00:00:00', 'Jul 01 2012 - Jul 31 2012');

SET @d1 = '2012-06-01 00:00:00'; -- Jun 01 2012
SET @d2 = '2012-07-01 00:00:00'; -- Jun 30 2012

1.1) Outside Query

SELECT * FROM table1 WHERE @d2 <= start_datetime OR end_datetime <= @d1;

-- start_datetime         end_datetime           comments
-- -------------------    -------------------    -------------------------
-- 2012-05-01 00:00:00    2012-06-01 00:00:00    May 01 2012 - May 31 2012
-- 2012-07-01 00:00:00    2012-08-01 00:00:00    Jul 01 2012 - Jul 31 2012

1.2) Overlap Query

SELECT * FROM table1 WHERE @d2 > start_datetime AND end_datetime > @d1;

-- start_datetime         end_datetime           comments
-- -------------------    -------------------    -------------------------
-- 2012-05-01 00:00:00    2012-06-16 00:00:00    May 01 2012 - Jun 15 2012
-- 2012-05-01 00:00:00    2012-08-01 00:00:00    May 01 2012 - Jul 31 2012
-- 2012-06-10 00:00:00    2012-06-20 00:00:00    Jun 10 2012 - Jun 19 2012
-- 2012-06-16 00:00:00    2012-08-01 00:00:00    Jun 16 2012 - Jul 31 2012

2) Example Data and Query for Inclusive End Dates

CREATE TABLE table2 (
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  comments VARCHAR(100) NOT NULL,
  KEY (start_date),
  KEY (end_date)
);

INSERT INTO table2 (start_date, end_date, comments) VALUES
('2012-05-01', '2012-05-31', 'May 01 2012 - May 31 2012'),
('2012-05-01', '2012-06-15', 'May 01 2012 - Jun 15 2012'),
('2012-05-01', '2012-07-31', 'May 01 2012 - Jul 31 2012'),
('2012-06-10', '2012-06-19', 'Jun 10 2012 - Jun 19 2012'),
('2012-06-16', '2012-07-31', 'Jun 16 2012 - Jul 31 2012'),
('2012-07-01', '2012-07-31', 'Jul 01 2012 - Jul 31 2012');

SET @d1 = '2012-06-01'; -- Jun 01 2012
SET @d2 = '2012-06-30'; -- Jun 30 2012

2.1) Outside Query

SELECT * FROM table2 WHERE @d2 < start_date OR end_date < @d1;

-- start_date    end_date      comments
-- ----------    ----------    -------------------------
-- 2012-05-01    2012-05-31    May 01 2012 - May 31 2012
-- 2012-07-01    2012-07-31    Jul 01 2012 - Jul 31 2012

2.2) Overlap Query

SELECT * FROM table2 WHERE @d2 >= start_date AND end_date >= @d1;

-- start_date    end_date      comments
-- ----------    ----------    -------------------------
-- 2012-05-01    2012-06-15    May 01 2012 - Jun 15 2012
-- 2012-05-01    2012-07-31    May 01 2012 - Jul 31 2012
-- 2012-06-10    2012-06-19    Jun 10 2012 - Jun 19 2012
-- 2012-06-16    2012-07-31    Jun 16 2012 - Jul 31 2012