Mysql检查日期范围在其他日期范围

Question

我有一个日期范围

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

现在我有了第二对日期范围，我想检查事件是否在日期范围内。例如

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

那么在这个范围内应该是"my event"， "my event 2"one_answers"my event 3"…

在这一刻，我只想试着得到我的范围之间的所有日期(所以在2014-03-10和2014-03-14之间)，并为它写一个大查询…但一定有更简单的方法来做到这一点，对吗?

Answer 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)))

Answer 2

使用此代码

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

Answer 3

试试这个:

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

我已经编辑了我的答案。