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.

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().

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 DAYOFMONTH(d)-1 DAY),INTERVALn MONTH)

For example, to find the first day of the previous and following months relative to a given date, n is -1 and 1:

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

It’s easier to find the last day of the month for a given date because there is a function for it:

mysql>SELECT d, LAST_DAY(d) AS 'last of month'
    -> FROM date_val;
+------------+---------------+
| d          | last of month |
+------------+---------------+
| 1864-02-28 | 1864-02-29    |
| 1900-01-15 | 1900-01-31    |
| 1987-03-05 | 1987-03-31    |
| 1999-12-31 | 1999-12-31    |
| 2000-06-04 | 2000-06-30    |
+------------+---------------+

For the general case, to find the last of the month for any month n months away from a given date, shift the date by that many months first and then pass it to LAST_DAY():

LAST_DAY(DATE_ADD(d,INTERVALn MONTH))

For example, to find the last day of the previous and following months relative to a given date, n is -1 and 1:

mysql>SELECT d,
    -> LAST_DAY(DATE_ADD(d,INTERVAL -1 MONTH))
    ->   AS 'last of previous month',
    -> LAST_DAY(DATE_ADD(d,INTERVAL 1 MONTH))
    ->  AS 'last of following month'
    -> FROM date_val;
+------------+------------------------+-------------------------+
| d          | last of previous month | last of following month |
+------------+------------------------+-------------------------+
| 1864-02-28 | 1864-01-31             | 1864-03-31              |
| 1900-01-15 | 1899-12-31             | 1900-02-28              |
| 1987-03-05 | 1987-02-28             | 1987-04-30              |
| 1999-12-31 | 1999-11-30             | 2000-01-31              |
| 2000-06-04 | 2000-05-31             | 2000-07-31              |
+------------+------------------------+-------------------------+

To find the length of a month in days, determine the date of its last day with LAST_DAY(), and then use DAYOFMONTH() to extract the day-of-month component from the result:

mysql>SELECT d, DAYOFMONTH(LAST_DAY(d)) AS 'days in month' FROM date_val;
+------------+---------------+
| d          | days in month |
+------------+---------------+
| 1864-02-28 |            29 |
| 1900-01-15 |            31 |
| 1987-03-05 |            31 |
| 1999-12-31 |            31 |
| 2000-06-04 |            30 |
+------------+---------------+

Performing Leap Year Calculations later in this chapter discusses how to calculate month lengths from within a program without using SQL. (The trick is that you must account for leap years.)