Retrieve records from table based on two datetime fields

Question

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?

to_date - NULL mean, this record is valid until today. since 2015-10-30 lies between second row. and '2016-08-08' lies in the third row, I need to be able to retrieve both these rows. Am I clearing myself @AlokPatel ? — Suman Bhandari
– Suman Bhandari, Commented Aug 11, 2016 at 4:09

1000111 · Accepted Answer · 2016-08-11 04:32:44Z

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())

edited Aug 11, 2016 at 4:32

answered Aug 11, 2016 at 4:00

1000111

13.5k2 gold badges32 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Suman Bhandari Over a year ago

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

1000111 Over a year ago

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?

Community · Accepted Answer · 2017-05-23 11:51:41Z

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

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Aug 12, 2016 at 1:52

Suman Bhandari

911 silver badge9 bronze badges

Collectives™ on Stack Overflow

Retrieve records from table based on two datetime fields

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related