comparing dates by month and year in mysql

15

votes

I have a table containing data about events and festivals with following columns recording their start and end dates.

Start_Date
End_Date

date format is in YYYY-MM-DD. I need to fetch event details with the following condition.

Need to fetch all events which start with a current month and there end dates can be anything say currentDate+next30days.

I am clear about end date concept. but not sure how I can fetch data whose start dates are in a current month. For this, I need to compare current year and current month against the Start_Date column in my database.

Can anyone help me to point out as how I can do that?

mysqlsql

26

votes

select * from your_table
where year(Start_Date) = year(curdate())
and month(Start_Date) = month(curdate())
and end_date <= curdate() + interval 30 day

5

votes

I don't like either of the other two answers, because they do not let the optimizer use an index on start_date. For that, the functions need to be on the current date side.

So, I would go for:

where start_date >= date_add(curdate(), interval 1 - day(curdate()) day) and
      start_date < date_add(date_add(curdate(), interval 1 - day(curdate()) day), interval 1 month)

All the date functions are on curdate(), which does not affect the ability of MySQL to use an index in this case.

You can also include the condition on end_date:

where (start_date >= date_add(curdate(), interval 1 - day(curdate()) day) and
       start_date < date_add(date_add(curdate(), interval 1 - day(curdate()) day), interval 1 month)
      ) and
      end_date <= date_add(curdate(), interval 30 day)

This can still take advantage of an index.

4

votes

DateTime functions are your friends:

SELECT
    *
FROM
    `event`
WHERE
    (MONTH(NOW()) = MONTH(`Start_Date`))
    AND
    (`End_Date` <= (NOW() + INTERVAL 30 DAY))
    AND
    (YEAR(NOW()) = YEAR(`Start_Date`))

2

votes

Comparing the year and month separately feels messy. I like to contain it in one line. I doubt it will make a noticeable difference in performance, so its purely personal preference.

select * from your_table
where LAST_DAY(Start_Date) = LAST_DAY(curdate())
and end_date <= curdate() + interval 30 day

So all I'm doing is using the last_day function to check the last day of the month of each date and then comparing this common denominator. You could also use

where DATE_FORMAT(Start_Date ,'%Y-%m-01') = DATE_FORMAT(curdate(),'%Y-%m-01')

comparing dates by month and year in mysql

4 Answers