Compare a date withing range of date fields in mysql I have this simple query that retrieving data:

select leave_detail_id, 
       emp_id, 
       leave_type, 
       leave_current_status_id, 
       leave_start_date, 
       leave_end_date
from tbl_emp_leave_details eld


Data:



Now I have this query, i want to filter my record based on date:

select leave_detail_id, 
       emp_id, 
       leave_type, 
       leave_current_status_id, 
       leave_start_date, 
       leave_end_date
from tbl_emp_leave_details eld
where leave_start_date &gt;= '2019-09-01'
and   leave_end_date &lt;= '2019-09-31'


Now it should bring the highlighted record but it shows nothing (no record found). What should I do bring the highlighted record? what is the problem with my query?
If you allow partial overlapping (not full include only as your code did) then you must compare (start1 with end2) and (start2 with end1):

where leave_end_date &gt;= '2019-09-01'
  and leave_start_date &lt;= '2019-09-31'


If you need the length of overlapping period then you need in

select datediff(greatest(leave_start_date, '2019-09-01'), 
                least(leave_end_date, '2019-09-31'))