mysql check date range in other date range

Question

I have a row with a date range

title | fromDate | tillDate
------------------------------------
my event | 2014-03-12 | 2014-03-13

my event 2 | 2014-03-14 | 2014-03-17

my event 3 | 2014-03-01 | 2014-03-10

my event 4 | 2014-03-01 | 2014-03-09

Now I have a second pair of date range and I want to check if the event is in the date range .. for example

checkStartDate = 2014-03-10 checkEndStartDate = 2014-03-14

So in the range would be "my event" , "my event 2" and "my event 3" ...

At this moment I want to just try and get ALL the dates between my range (so between 2014-03-10 and 2014-03-14) and write one big query for it ... But there must be an easier way to do this right?

possible duplicate of [MySQL range date overlap check](http://stackoverflow.com/questions/2545947/mysql-range-date-overlap-check) — Tom Fenech, Mar 11 '14 at 09:25

Laukik Patel · Answer 1 · 2014-03-11T09:35:20.123

1

SELECT title FROM table WHERE (DATE('2014-03-10') BETWEEN DATE(fromDate) AND DATE(tillDate) OR DATE('2014-03-14') BETWEEN DATE(fromDate) AND DATE(tillDate) OR (DATE('2014-03-10') <= DATE(fromDate) AND DATE('2014-03-14') >= DATE(tillDate)))

edited Mar 11 '14 at 09:35

answered Mar 11 '14 at 09:25

Laukik Patel

733
7
18

user983983 · Answer 2 · 2014-03-11T10:36:14.617

0

Try this:

select title from table
where checkStartDate  <= fromDate 
and checkEndStartDate <= tillDate

I have edited my answer.

edited Mar 11 '14 at 10:36

answered Mar 11 '14 at 09:23

user983983

158
1
2
11

score 0 · Answer 3 · answered Mar 11 '14 at 09:34

0

use this code

select *  from events_table  where fromDate >= checkStartDate   and tillDate <= checkEndStartDate

answered Mar 11 '14 at 09:34

Ananth

1,520
3
15
28

mysql check date range in other date range

3 Answers3