'MySQL Select query between dates

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   |
mysqlsql


Solution 1:[1]

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

Solution 2:[2]

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

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 arcbjorn
Solution 3 chandu

Related Questions