How to filter dates in mysql? -

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

for example, user selecting start date 15-01-2016 , end date 30-01-2016. how can rows of date (l_date) contains date between user selected start date , end date?

i got struck : in below image (l_date) 2016-01-29, 2016-01-30, 2016-02-01, 2016-02-02. here how can row too, because user range till 2016-01-30 end date (l_end_date) had stored 2016-02-02 ignoring.

i tried:

  select l_date,l_start_date,l_end_date   `dates`   (l_end_date >= '2016-01-15' or l_end_date <= '2016-01-15') ,         (l_start_date >= '2016-01-15' or l_end_date <= '2016-01-15') ,          (l_end_date >= '2016-01-30' or l_end_date <= '2016-01-30') ,         (l_start_date >= '2016-01-30' or l_end_date <= '2016-01-30')  

you can use following logic:

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

the rule simple. 2 intervals overlap if 1 starts before second ends. , first ends after second starts.

for complete overlap, logic little different:

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

also note, inequalities might strict inequalities ("<" , ">"), depending on how intervals defined.