Selecting Overlapping Ranges in SQL

Warning - Old Content

This post is quite old, and it might not apply anymore, or maybe there's a better way to do the same thing nowadays. Take with a big grain of salt.

It comes up occasionally that you are storing some events in a DB with some duration, and you want to find all of those events that overlap a given date range. (At work, I needed to find out who was on vacation during a given period; it comes up more often than you’d think). It turns out that it’s a fairly simple SQL query to accomplish this. First, assume your DB table looks like:

name startDate endDate
foo 2006-01-03 2006-01-12
bar 2006-01-05 2006-01-20
baz 2006-01-07 2006-01-08
qux 2006-02-07 2006-02-18

To pull out all row that overlap with Jan 6th through 7th:

SELECT * FROM someTable WHERE endDate >= '2006-01-06' AND startDate <= '2006-01-07';

This will return foo, bar, and baz. Draw some diagrams to confirm this works; diagrams are the best starting point for this sort of problem.