Compare a date withing range of date fields in mysql
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'))