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:
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.