MySQL Cookbook, 2nd Edition

Finding the First Day, Last Day, or Length of a Month

Problem

Given a date, you want to determine the date for the first or last day of the month in which the date occurs, or the first or last day for the month n months away. A related problem is to determine the number of days in a month.

Solution

To determine the date for the first day in a month, use date shifting (an application of date arithmetic). To determine the date for the last day, use the LAST_DAY() function. To determine the number of days in a month, find the date for the last day and use it as the argument to DAYOFMONTH().

Discussion

Sometimes you have a reference date and want to reach a target date that doesn’t have a fixed relationship to the reference date. For example, the first or last days of the current month aren’t a fixed number of days from the current date.

To find the first day of the month for a given date, shift the date back by one fewer days than its DAYOFMONTH() value:

mysql>SELECT d, DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY) AS '1st of month'
    -> FROM date_val;
+------------+--------------+
| d          | 1st of month |
+------------+--------------+
| 1864-02-28 | 1864-02-01   |
| 1900-01-15 | 1900-01-01   |
| 1987-03-05 | 1987-03-01   |
| 1999-12-31 | 1999-12-01   |
| 2000-06-04 | 2000-06-01   |
+------------+--------------+

In the general case, to find the first of the month for any month n months away from a given date, calculate the first of the month for the date, and then shift the result by n months:

DATE_ADD(DATE_SUB(d,INTERVAL ...