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

Question

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

`SELECT * FROM tbl WHERE start_date <= '12-05-2016' AND expiry_date >= '10-05-2016'` — Felix Pamittan, May 24 '16 at 05:29
It's a shame if mySQL does not support `BETWEEN` EDIT: mySQL does support `BETWEEN` and I read this question wrong :) — Michael Z., May 24 '16 at 05:32

score 1 · Accepted Answer · edited May 23 '17 at 10:28

1

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'

edited May 23 '17 at 10:28

Community

1
1

answered May 24 '16 at 05:30

Felix Pamittan

31,544
7
41
67

score 0 · Answer 2 · answered May 24 '16 at 05:30

0

Hoping that start_date is always less than expiry_date

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

answered May 24 '16 at 05:30

Naruto

4,221
1
21
32

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

2 Answers2