How to filter dates in mysql?

Question

I have start date (l_start_date), end date (l_end_date) and dates (l_date) in database.

For an example, User is selecting start date as 15-01-2016 and end date as 30-01-2016. How can I get all the rows of date (l_date) which contains the date between the user selected start date and end date?

I got struck : In the below image (l_date) as 2016-01-29, 2016-01-30, 2016-02-01, 2016-02-02. here how can i get this row too, because user range is till 2016-01-30 but end date (l_end_date) i had stored 2016-02-02 so its ignoring.

I tried:

  SELECT l_date,l_start_date,l_end_date
  FROM `dates`
  WHERE (l_end_date >= '2016-01-15' or l_end_date <= '2016-01-15') AND
        (l_start_date >= '2016-01-15' or l_end_date <= '2016-01-15') AND 
        (l_end_date >= '2016-01-30' or l_end_date <= '2016-01-30') AND
        (l_start_date >= '2016-01-30' or l_end_date <= '2016-01-30')

Seems similar to this one: http://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates — SenselessCoder, Jan 16 '16 at 12:48

score 1 · Answer 1 · answered Jan 16 '16 at 12:50

You can use the following logic:

where l_end_date >= '2016-01-15' and   -- the input start date
      l_start_date <= '2016-01-30'     -- the input stop date

The rule is simple. Two intervals overlap if one starts before the second ends. And the first ends after the second starts.

For complete overlap, the logic is a little different:

where l_end_date >= '2016-01-30' and   -- the input stop date
      l_start_date <= '2016-01-15'     -- the input start date

Also note, the inequalities might be strict inequalities ("<" and ">"), depending on how the intervals are defined.

score 0 · Answer 2 · answered Jan 16 '16 at 13:20

The answer given by @Gordon Linoff explains it very good, but to translate into a context you might understand:

your criteria isn't a criteria at all.

(l_end_date >= '2016-01-15' or l_end_date <= '2016-01-15')

will return all records, because if the end_date isn't >= it is <= the end date, that's logic. The same goes for the next lines. So you actually don't have a where clause at all. The key word here is or.

Try the query provided by @Gordon Linoff

 SELECT l_date,l_start_date,l_end_date
  FROM `dates`
  where l_end_date >= '2016-01-30' and   -- the input stop date
      l_start_date <= '2016-01-15'     -- the input start date

How to filter dates in mysql?

2 Answers2