MySQL: finding rows with conflicting date ranges

I am writing an ad booking system for our site. Ads are booked in the box StartDate

and EndDate

to indicate how long they will be online.

When adding a new listing, I ran a quick check to make sure the new listing didn't clash with an existing listing booked in the same position.

I thought I had this with this query:

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

      

        
        
        
      

    

2010-4-22 is StartDate

for a new ad that I want to order. There is already a registered advertisement, in which StartDate

- 2010-04-26, and EndDate

- 2010-05-09.

These are the query results with different dates:

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

• try to put the ad at the beginning and end DURING the existing ad date range: returns 1 row (correct)

• try putting the declaration at the beginning and end BEFORE the beginning of the existing declaration: returns 0 lines (Correct)

• try to start an ad starting before the start of an existing ad and keeping DURING the existing ad date range: returns 0 lines (incorrect)

• try loading an ad starting before the end and ending AFTER the current ad ends: returns 0 lines (wrong!)

Does anyone have any ideas how to rewrite this query so that I can make sure it only encounters collisions on date ranges?

Thanks, Matt