CODEWITHSUNDEEP
mysqlsqldate
Alyas
Alyas

Reputation: 630

How to check if selected date range is between another date range

I am in the situation where i want to match a date range with another date range, it might be simple but i am stuck at it.

Below is table structure

Table - lifecycles

then a few records as below

1 - 07/23/2013 - 07/24/2013
2 - 07/15/2013 - 07/25/2015
3 - 03/10/2013 - 03/10/2014

Now i want to search these records by date range and want to see if some life exists in that range; e.g. i want to find the lives between 08/01/2013 - 01/01/2014

As expected result it should select the life#2 and life#3

How can this be done with MySQL query?

Any help is highly appreciated.

Upvotes: 0

Views: 3458

Answers (3)

Ritesh Patadiya
Ritesh Patadiya

Reputation: 119

This might help you.

SELECT SUM( IF( '2014-01-02' BETWEEN from_date AND to_date, 1, 0 ) ) AS from_exist,
       SUM( IF( '2014-02-12' BETWEEN from_date AND to_date, 1, 0 ) ) AS to_exist
FROM date_range

So based on the results you can check whether date is between existing date range or not.

Upvotes: 1

RenkyaZ
RenkyaZ

Reputation: 108

So you want to exclude lifes that are ended BEFORE 08/01/2013 and the ones that are not started AFTER 01/01/2014. This should work:

SELECT *
  FROM lifecycles as alive
 WHERE NOT EXISTS (
       SELECT 1
         FROM lifecycles as dead
        WHERE dead.life_id = alive.life_id
          AND (str_to_date(life_start_date, '%m/%d/%Y') > '2014-01-01'
           OR str_to_date(life_end_date, '%m/%d/%Y') < '2013-08-01'))

Upvotes: 0

Zagor23
Zagor23

Reputation: 1963

This query should do it:

SELECT 
    *
FROM
    lifecycles
WHERE
    str_to_date(life_start_date, '%m/%d/%Y') <= '2014-01-01' 
    AND str_to_date(life_end_date, '%m/%d/%Y') >= '2013-08-01';

Which basically means life hasn't started before the end of the range you are looking for, and life didn't end before the range start.

Since you keep dates in VARCHAR format, you need to use str_to_date function, which is bad since MySQL won't be able to utilize any possible indexes you have on start_date or end_date columns.

Upvotes: 2

Related Questions