-1

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?

Suman Bhandari
  • 91
  • 1
  • 9

2 Answers2

1

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())
1000111
  • 13,169
  • 2
  • 28
  • 37
  • to_date - NULL means, this record is valid until today. since '2015-10-30' lies between second row. and '2016-08-08' lies between third row, I need to be able to retrieve both these rows – Suman Bhandari Aug 11 '16 at 04:14
  • One of the weird date range searches I've ever seen. No problem. Just tell me ` '2015-10-30' and '2016-08-08'` here are two dates you want to search with them. Is it always fixed to two? – 1000111 Aug 11 '16 at 04:29
0

Finally, I found out what I was looking for.

select * from table where
end_date >= start_search && start_date <= end_search

More detailed answer in the link below. Answered by pacerier

mysql-query-for-certain-date-range

Community
  • 1
  • 1
Suman Bhandari
  • 91
  • 1
  • 9