Suman Bhandari Suman Bhandari - 3 months ago 9
MySQL Question

Retrieve records from table based on two datetime fields

I have the following data in the table

from_date to_date
2015-05-12 2015-10-20
2015-10-21 2016-02-02
2016-02-03 NULL


Where NULL value in to_date denotes that this record is valid until this time.

The records I want to retrieve is between '2015-10-30' and '2016-08-08'. How do I get the second and third rows based on my search criteria?

Answer

I am confused why are you expecting the second row in the result set. Is it something loose range searching (either by from_date or by to_date)?

You can try something like that:

SELECT 
*
FROM your_table
WHERE from_date >= startDate
AND IF(to_date IS NULL, TRUE, to_date <= endDate);

Note: Here startDate and endDate are the dates in your given range.

EDIT:

SELECT 
 *
FROM your_table
WHERE 
'2015-10-30' BETWEEN from_date AND COALESCE(to_date,CURDATE())
OR 
'2016-08-08' BETWEEN from_date AND COALESCE(to_date,CURDATE())