Find Events between Dates I have table with following data


id   start_date end_date
1    2018-01-01 2018-01-15
2    2018-01-11 2018-01-16
3    2018-01-03 2018-02-10
4    2018-01-01 2018-03-15


I want to list events based on months. If I am on 2018-01 it gives results proper but when I am in 2018-02 it does not give me entry 4 which spread between 2018-01-01 to 2018-03-15

SELECT * FROM `table` `t` 
WHERE  (start_date &gt;='2018-01-01' AND start_date &lt;='2018-01-31') 
OR     (end_date &gt;= '2018-01-01'  AND   end_date &lt;='2018-01-31')


Any help
What about using BETWEEN by taking the first and last month of the ranges:

SELECT *
FROM `table` `t`
WHERE 
  '2018-01-01' BETWEEN
     DATE_FORMAT(start_date ,'%Y-%m-01') AND
     LAST_DAY(end_date)


As this won't use indexes an alternate is to use generated columns:

ALTER TABLE `table`
ADD start_month DATE GENERATED ALWAYS AS (DATE_FORMAT(start_date ,'%Y-%m-01')),
ADD end_month DATE GENERATED ALWAYS AS (LAST_DATE(end_date)),
ADD INDEX start_end_month(start_month,end_month)


Then the query will become the following and use the start_end_month index:

SELECT *
FROM `table` `t`
WHERE 
  '2018-01-01' BETWEEN
     start_month AND end_month