MySQL query to select events between start/end date

You need the events that start and end within the scope. But that's not all: you also want the events that start within the scope and the events that end within the scope. But then you're still not there because you also want the events that start before the scope and end after the scope.

Simplified:

  1. events with a start date in the scope
  2. events with an end date in the scope
  3. events with the scope startdate between the startdate and enddate

Because point 2 results in records that also meet the query in point 3 we will only need points 1 and 3

So the SQL becomes:

SELECT * FROM events 
WHERE start BETWEEN '2014-09-01' AND '2014-10-13' 
OR '2014-09-01' BETWEEN start AND end

If I understood correctly you are trying to use a single query, i think you can just merge your date search toghter in WHERE clauses

SELECT id 
FROM events 
WHERE start BETWEEN '2013-06-13' AND '2013-07-22' 
AND end BETWEEN '2013-06-13' AND '2013-07-22'

or even more simply you can just use both column to set search time filter

SELECT id 
FROM events 
WHERE start >= '2013-07-22' AND end <= '2013-06-13'

Here lot of good answer but i think this will help someone

select id  from campaign where ( NOW() BETWEEN start_date AND end_date)