Ankit Tater Ankit Tater - 6 months ago 13
MySQL Question

SQL query to find all Data by date range which falls between two dates

Task Table
id name start_date expiry_date
1 Task 1 09-05-2016 11-05-2016
2 Task 2 10-05-2016 13-05-2016
3 Task 3 11-05-2016 12-05-2016
4 Task 4 17-05-2016 20-05-2016
5 Task 5 18-05-2016 26-05-2016


I need to find all the task from a given date range where each date from range falls between start_date and expiry_date. suppose input is 10-05-2016 to 12-05-2016.

id name start_date expiry_date
1 Task 1 09-05-2016 11-05-2016
2 Task 2 10-05-2016 13-05-2016
3 Task 3 11-05-2016 12-05-2016

Answer

You can use this condition to check if two date ranges overlap:

WHERE 
    StartA <= EndB 
    AND EndA >= StartB

Your query should be:

SELECT *
FROM tbl
WHERE
    start_date <= '12-05-2016'
    AND expiry_date >= '10-05-2016'