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? — WOUNDEDStevenJones
– WOUNDEDStevenJones, Commented May 5, 2022 at 18:22

stefan · Accepted Answer · 2022-05-07 08:59:41Z

2

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

edited May 7, 2022 at 8:59

answered May 5, 2022 at 19:00

stefan

2,2622 gold badges15 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

WOUNDEDStevenJones Over a year ago

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 Over a year ago

See dbfiddle.uk/… 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).

stefan Over a year ago

@WOUNDEDStevenJones brilliant! Thanks for your suggestion. May I add your solution into my answer?

WOUNDEDStevenJones Over a year ago

absolutely, go for it

arcbjorn · Accepted Answer · 2022-05-05 18:13:11Z

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 5, 2022 at 18:13

arcbjorn

1211 silver badge4 bronze badges

1 Comment

stefan Over a year ago

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

chandu · Accepted Answer · 2022-05-05 18:20:03Z

-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 5, 2022 at 18:20

chandu

971 silver badge8 bronze badges

1 Comment

WOUNDEDStevenJones Over a year ago

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)

Collectives™ on Stack Overflow

MySQL Select query between dates

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related