MySQL Select query between dates

Question

I have a MySql table called contracts. I try to filter contracts applicable between two dates 2022-05-03 and 2022-05-07. MySQL query must be return id numbers: 1,2,3 because all record have at least one contract applicable day in filter data. Any pointers into the right direction would be helpful. Thanks!

SELECT id FROM contracts WHERE ...

contracts table

|     id    | start_date |  end_date  |
|      1    | 2022-05-01 | 2022-05-04 |
|      2    | 2022-05-06 | 2022-05-10 |
|      3    | 2022-05-01 | 2022-05-10 |

calendar visualisation

| id  | 05/01 | 05/02 | 05/03 | 05/04 | 05/05 | 05/06 | 05/07 | 05/08 | 05/09 | 05/10 |
|  1  |   x   |   x   |   x   |   x   |       |       |       |       |       |       |
|  2  |       |       |       |       |       |   x   |   x   |   x   |   x   |   x   |
|  3  |   x   |   x   |   x   |   x   |   x   |   x   |   x   |   x   |   x   |   x   |

Does this answer your question? [Select mysql query between date?](https://stackoverflow.com/questions/1469689/select-mysql-query-between-date) — WOUNDEDStevenJones, May 05 '22 at 18:22

stefan · Answer 1 · 2022-05-07T08:59:41.657

It seems that you need a query that can "detect" 4 possibilities: {1} start before the interval (start <-> end), end inside the interval {2} start and end inside the interval {3} start inside the interval, end after the interval {4} start before the interval, end after the interval

                 start                        end
-------------------|---------------------------|--------------

{1} ----------|------------|----------------------------------

{2} -------------------|---------------|----------------------

{3} ------------------------------|--------------------|------

{4} ----------|-----------------------------------|-----------

Thus:

select id
from Contracts 
where 
( start_date <= '2022-05-03' and ( end_date >= '2022-05-03' and end_date <= '2022-05-07' ) ) 
or
( start_date >= '2022-05-03' and end_date <= '2022-05-07' ) 
or
( ( start_date >= '2022-05-03' and start_date <= '2022-05-07' ) and end_date >= '2022-05-07' )
or
( start_date <= '2022-05-03' and end_date >= '2022-05-07' )
;

DBfiddle here (data set slightly larger than your sample data).

EDIT

As @WOUNDEDStevenJones has suggested, the following query is easier to code and understand, AND will deliver reliable results:

select id
from Contracts 
WHERE start_date <= '2022-05-07' AND end_date >= '2022-05-03'
;

DBfiddle

One way this can be simplified is to check `WHERE start_date <= '2022-05-07' AND end_date >= '2022-05-03'` instead of the 4 conditions separately. — WOUNDEDStevenJones, May 05 '22 at 20:26
See https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=2c0836460fa44f516fc4ae7517a2329e for a working example (with all 4 of your example cases, plus 1 with both start/end before 2022-05-03, and 1 with both start/end after 2022-05-07). — WOUNDEDStevenJones, May 05 '22 at 20:36
@WOUNDEDStevenJones brilliant! Thanks for your suggestion. May I add your solution into my answer? — stefan, May 05 '22 at 21:01

score 1 · Answer 2 · answered May 05 '22 at 18:13

1

This should work:

SELECT id FROM Contracts WHERE start_date BETWEEN 2022-05-03 and 2022-05-07 ORDER BY start_date;

Docs: https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html

answered May 05 '22 at 18:13

arcbjorn

121
1
4

It's about a range ie an interval between the start and end, which should be compared to another range (not just a start_date). – stefan May 05 '22 at 18:49

score -1 · Answer 3 · answered May 05 '22 at 18:20

-1

this should work

SELECT id FROM `Contracts ` WHERE 
(DATE(start_date) >= '2022-05-03' || DATE(start_date) <= '2022-05-03') ||
(DATE(end_date) >= '2022-05-07' || DATE(end_date) <= '2022-05-07')

answered May 05 '22 at 18:20

chandu

97
1
7

Wouldn't that select all data with a valid `start_date` or `end_date`? You have `WHERE (start >= 3 OR start <= 3) OR (end >= 7 OR end <= 7)` – WOUNDEDStevenJones May 05 '22 at 18:25

MySQL Select query between dates

3 Answers3