MySQL: Finding rows with conflicting date ranges

Question

Hi there,

I'm writing an ad booking system for our site. Ads are booked in with a StartDate and EndDate field to indicate how long they're online for.

When adding a new advert, I have a quick validation check to make sure the new ad doesn't clash with an existing ad that's booked into the same position.

I thought I had it with this query:

SELECT * 
FROM adverts 
WHERE EndDate >= '2010-04-22' 
AND StartDate < '2010-04-22'

2010-4-22 is the StartDate for the new advert I want to book. There is an advert already booked in whose StartDate is 2010-04-26 and EndDate is 2010-05-09.

These are the results of the query with different dates:

---

• try to book an ad starting AFTER the existing ad ends: returns 0 rows (correct)

• try to book an ad starting and ending DURING the existing ad's date range: returns 1 row (correct)

• try to book an ad starting and ending BEFORE the existing ad begins: returns 0 rows (correct)

• try to book an ad starting BEFORE the existing ad starts, and enduring DURING the existing ad's date range: returns 0 rows (incorrect)

• try to book an ad starting BEFORE and ending AFTER the current ad ends: returns 0 rows (incorrect!)

---

Does anybody have any ideas how to rewrite this query so I can make sure it only picks up clashes in the date ranges?

Thanks, Matt

Answer 1

SELECT *
  FROM adverts
 WHERE `EndDate` >= :AdStartDate
   AND `StartDate` <= :AdEndDate

I haven't accounted for the edge case where start and end dates are equal, since I don't know what your rules are for that.