sql - Mysql query - Return dates where two date ranges intersect

Question

I am trying to write a MySql query, where two date ranges intersect.

For instance, I want to return a row from a table where a given date exists.

So my user can select two dates 'startdate', 'enddate'

I have a lookup table

id, fromdate, tildate

In this table I have 2 rows

id:1, fromdate:'2021-01-03', tildate:'2021-01-05'
id:2, fromdate:'2021-01-05', tildate:'2021-01-08'

Now If the user queries the database with values:

startdate: '2021-01-01',  enddate '2021-01-02' - Should return nothing
startdate: '2021-01-03',  enddate '2021-01-04' - Should return 1, since there is an intersection
startdate: '2021-01-02',  enddate '2021-01-06' - Should return id 1 and 2, since there is an intersection in both
startdate: '2021-01-05',  enddate '2021-01-05' - Should return id 1 and 2, since there is an intersection in both
startdate: '2021-01-07',  enddate '2021-01-08' - Should return 2, since there is an intersection

I have tried this query

SELECT 
   id, fromdate, todate FROM table_name 
WHERE 
    ('2021-01-02' >= fromDate
     AND '2021-01-02' <= toDate)
OR
    ('2021-01-06' >= fromDate
    AND '2021-01-06' <= toDate)

This doesn't work.

Hopes this makes sense

question from:https://stackoverflow.com/questions/65908726/mysql-query-return-dates-where-two-date-ranges-intersect

Answer 1

try this:

SELECT *
FROM table_name 
WHERE 
    ('2021-01-02' >= fromDate
     AND '2021-01-02' <= toDate)
OR
    ('2021-01-06' >= fromDate
    AND '2021-01-06' <= toDate)

You used fromDate everywhere in your query, that might've been the problem