Tag: date calculations

MySQL select rows between months

Let’s say you want all rows from a table that have a date between next month and the next 3 months, MySQL provide us (since 4.1) a beautiful LAST_DAY function (for the record there is no FIRST_DAY…. at least yet), and the DATE_ADD function (actually there is also a DATE_SUB).

Getting first day of next month:

SELECT DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY);

Getting the last day of the next third calendar month

SELECT LAST_DAY(DATE_ADD(NOW(), INTERVAL 3 MONTH));

So, from here is pretty simple:

SELECT * FROM table WHERE 
date_field > DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY) AND 
date_field < LAST_DAY(DATE_ADD(NOW(), INTERVAL 3 MONTH));

Bonus query:

get first day of current month

SELECT CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE)