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   |

user_8152775 · Answer

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

user_13143157 · Answer

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

user_15185155 · Answer

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

MySQL Select query between dates

3 Answers