MySql query where any date between two dates is between two dates

Question

I have a MySQL Datatable with an ID, two datetime and a boolean column.

Looks like this:

CREATE TABLE `Example` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `Date1` date NOT NULL,
 `Date2` date NOT NULL,
 `Answered` tinyint(1) DEFAULT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Now I have a startdate and an enddate, and I need a sql query which gives me that rows, where any day between the startdate and the enddate is between Date1 and Date2!

What works is that query, wich gives me those rows, where the Date1 is between the startdate and the enddate and Aswered = 1:

    $proof = "select * from Example where Date1 
    between '$startdate' and '$enddate' and Answered = 1";

But I dont know how to do that with all dates between two dates

Thanks for help!

Possible duplicate of [Determine Whether Two Date Ranges Overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) — Juan Carlos Oropeza, Mar 30 '16 at 21:09
You should include sample data and desire output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, Mar 30 '16 at 21:11
I have no sample data and the expected result should be an array of datatablerows where any day between a startdate and an enddate is between Date1 and Date2 — DST, Mar 30 '16 at 21:15
If you dont have sample data how will you test your query? Dont be lazy and prepare a proper question. — Juan Carlos Oropeza, Mar 30 '16 at 21:20

score 1 · Answer 1 · answered Mar 30 '16 at 21:42

Ok based on the 2 answers above I solved my problem, this is the query I need:

SELECT * FROM `Example`
WHERE ('$startdate' BETWEEN Date1 AND Date2) 
    OR ('$enddate' BETWEEN Date1 AND Date2)
    OR (Date1 BETWEEN '$startdate' AND '$enddate') 
    OR (Date2 BETWEEN '$startdate' AND '$enddate') 
    AND Answered = 1

score 0 · Answer 2 · answered Mar 30 '16 at 21:13

0

If I'm understanding correctly you'd want:

SELECT * FROM Example 
WHERE (Date1 BETWEEN '$startdate' AND '$enddate') 
    AND (Date2 BETWEEN '$startdate' AND '$enddate') 
    AND Answered = 1

answered Mar 30 '16 at 21:13

Samuel Cook

16,620
7
50
62

I want to know if any Day from the timespan between the startdate and the enddate is inside the timespan between Date1 and Date2 – DST Mar 30 '16 at 21:23
that SQL statement should do it – Samuel Cook Mar 30 '16 at 21:52

Craig van Tonder · Answer 3 · 2016-03-30T21:21:16.200

0

If you want to include the start and end points, what about:

SELECT * 
FROM `Example` 
WHERE `Answered` = 1
AND `Date1` >= '$startdate'
AND `Date2` <= '$enddate'

If you do not want to include them in the results:

SELECT * 
FROM `Example` 
WHERE `Answered` = 1
AND `Date1` > '$startdate'
AND `Date2` < '$enddate'

You should probably read here too: PHP: Prepared Statements - Manual

edited Mar 30 '16 at 21:21

answered Mar 30 '16 at 21:15

Craig van Tonder

7,497
18
64
109

Thats not that what I am searching for, I want to know if any Day from the timespan between the startdate and the enddate is inside the timespan between Date1 and Date2 – DST Mar 30 '16 at 21:30

score 0 · Answer 4 · answered Mar 14 '19 at 09:16

Below query will give you exact data between two dates with answered 1

SELECT * FROM `Example`
WHERE ( `Date1` <= '$startdate' and `Date2` >= '$startdate' )
OR ( '$startdate' <= `Date1`  and '$enddate' >= `Date1` )
OR ( `Date1` < '$enddate' and `Date2` >= '$enddate' )
AND `Aswered` = 1

MySql query where any date between two dates is between two dates

4 Answers4