Retrieve all records that occur within specific date range in MySQL

Question

I have a table that contains several contracts, and each contract has a start date and an end date, like this:

|  ID   |   Contract Name   |  Start Date  |  End Date  |
|-------|-------------------|--------------|------------|
|   1   |  Joe Bloggs       |  2012-01-01  | 2012-02-05 |
|   2   |  John Smiths      |  2012-02-01  | 2012-02-20 |
|   3   |  Johnny Briggs    |  2012-03-01  | 2012-03-20 |

What I am trying to do is build a query that will retrieve contracts that were active between a specific time period. So if I had the start date of 2012-02-10 and an end date of 2012-03-21 I should have the following contracts displayed:

|  ID   |   Contract Name   |  Start Date  |  End Date  |
|-------|-------------------|--------------|------------|
|   2   |  John Smiths      |  2012-02-01  | 2012-02-20 |
|   3   |  Johnny Briggs    |  2012-03-01  | 2012-03-20 |

My problem though is that I don't know how to build the query to do this. This is what I've got so far:

SELECT *
FROM contracts c
WHERE c.startdate BETWEEN '2012-02-10'
    AND '2012-03-21'
    AND c.enddate BETWEEN '2012-02-10'
    AND '2012-03-21'

This doesn't work though, no records are retrieved. What am I doing wrong?

ID=2 the Start Date is before the start date you are searching for. — JoDev
– JoDev, Commented Mar 20, 2013 at 10:41

Vinayagam · Accepted Answer · 2013-10-23 10:28:48Z

4

SELECT * FROM contracts
                WHERE (START_DATE between '2012-03-01' AND '2013-03-21')
                OR (END_DATE between '2012-03-01' AND '2013-03-21')
                OR (START_DATE<= '2012-03-01' AND END_DATE >='2013-03-21');

Check the SQL fiddle

edited Oct 23, 2013 at 10:28

answered Mar 21, 2013 at 14:03

Vinayagam

1,0021 gold badge10 silver badges29 bronze badges

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

Comments

reto · Accepted Answer · 2013-03-21 14:43:53Z

3

Er, time is linear right?

SELECT * 
FROM contracts 
WHERE end_date >= '2012-02-10' 
AND start_date <= '2012-03-21';

Let me illustrate...

    A-------------B
<------->
       <------>
           <----------->
<---------------------->

In all cases above, the start date is less than B. The end date is greater than A.

edited Mar 21, 2013 at 14:43

reto

16.8k8 gold badges56 silver badges70 bronze badges

answered Mar 20, 2013 at 10:59

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Comments

JoDev · Accepted Answer · 2013-03-20 10:42:29Z

0

For me, a good request will be

SELECT * FROM contracts c WHERE c.startdate >'2012-02-10' AND c.enddate < '2012-03-21'

answered Mar 20, 2013 at 10:42

JoDev

6,9231 gold badge25 silver badges37 bronze badges

Comments

Muhammad Raheel · Accepted Answer · 2013-03-20 10:49:10Z

0

It should have been like this

SELECT *
FROM contracts c
WHERE c.startdate >= '2012-02-10'
    AND c.enddate <= '2012-03-21'

answered Mar 20, 2013 at 10:49

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

Comments

Aris · Accepted Answer · 2020-04-21 11:59:27Z

0

    SELECT * FROM contracts
    WHERE 
    (START_DATE between '2012-03-01' AND '2013-03-21')
    OR (END_DATE between '2012-03-01' AND '2013-03-21')
    OR (START_DATE<= '2012-03-01' AND END_DATE >='2013-03-21');

explanation:

(START_DATE between '2012-03-01' AND '2013-03-21')

: intervals records that start between the input dates. First part or all of interval might be included.

(END_DATE between '2012-03-01' AND '2013-03-21')

: intervals that end between the input dates. Last part or all of interval might be included.

(START_DATE<= '2012-03-01' AND END_DATE >='2013-03-21')

: input dates are included within one interval only

answered Apr 21, 2020 at 11:59

Aris

5,1131 gold badge47 silver badges42 bronze badges

Collectives™ on Stack Overflow

Retrieve all records that occur within specific date range in MySQL

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related