Check Date between two date columns from two dates mySQL

Question

I want to check that record exist or not.

Table1:

  S.No    StartDate         EndDate
 -------------------------------------
  1.      2019-10-15        2019-10-20
  2.      2019-10-10        2019-10-13
  3.      2019-10-21        2019-10-25

I need to get data if any of the condition gets true according to any below scenarios.

Case 1: If date_from: 2019-10-17 and date_to: 2019-10-19

Output:

  S.No    StartDate         EndDate
 -------------------------------------
  1.      2019-10-15        2019-10-20

Case 2: If date_from: 2019-10-14 and date_to: 2019-10-21

Output:

  S.No    StartDate         EndDate
 -------------------------------------
  1.      2019-10-15        2019-10-20
  3.      2019-10-21        2019-10-25

Case 3: If date_from: 2019-10-13 and date_to: 2019-10-16

Output:

  S.No    StartDate         EndDate
 -------------------------------------
  1.      2019-10-15        2019-10-20
  2.      2019-10-10        2019-10-13

Case 4: If date_from: 2019-10-17 and date_to: 2019-10-20

Output:

  S.No    StartDate         EndDate
 -------------------------------------
  1.      2019-10-15        2019-10-20

Case 5: If date_from: 2019-11-17 and date_to: 2019-11-20

Output:

  *No records

Here what I had tried:

Query1:

SELECT * FROM Table1
WHERE StartDate BETWEEN **date_from** AND **date_to**
OR EndDate BETWEEN **date_from** AND **date_to**

Query2:

SELECT * FROM Table1
WHERE **date_from** BETWEEN StartDate AND EndDate 
OR **date_to** BETWEEN StartDate AND EndDate

I had already tried below solutions:

Check two date was not between two another date + MYSQl

check given date exists between two date column in mysql

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2019-03-14T07:13:26

This is the overlapping date range problem. Here is a query for your first scenario, where you want to find matching records between 2019-10-17 and 2019-10-19:

SELECT *
FROM yourTable
WHERE EndDate >= '2019-10-17' AND StartDate <= '2019-10-19';

More generally:

SELECT *
FROM yourTable
WHERE EndDate >= <date_from> AND StartDate <= <date_to>;

If you wanted to include say two date ranges in a single query, then you may simply expand the WHERE clause:

SELECT *
FROM yourTable
WHERE
    (EndDate >= '2019-10-13' AND StartDate <= '2019-10-16') OR
    (EndDate >= '2019-10-17' AND StartDate <= '2019-10-19');

Check Date between two date columns from two dates mySQL

2 Answers