Complicated Date Range Query in MySQL

Question

Hi, I was hoping someone could help with a complicated date range SQL query.

I need to find a whole bunch of records that could be: 1) within the searched date range
2) outside of the searched range but run through it
3) outside left of the searched range, starting before but ending during it
4) outside right of the searched range, starting during and ending after.

It's more complicated than I thought!

So far I've captured the first two.

AND (
  (event_dates.date_start <= *startdate* AND event_dates.date_end >= *enddate*)
  OR (event_dates.date_start >= *startdate* AND event_dates.date_end <= *enddate*)
)

How can include the last two without pulling everything before and after the search range?

Many thanks,
Chris

Answer 1

If you want all records that could touch the date range, then you only want to exclude the ones that start after the end of the range or end before the start of the range:

NOT (date_start > *enddate* or date_end < *startdate*)

That's equivalent to

date_start <= *enddate* and date_end >= *startdate*