MySQL Select query between dates-CodePudding

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   |

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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