Finding First and Last Days of Months

5.22.1 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.

5.22.2 Solution

You can do this by date shifting.

5.22.3 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, to find the last day of the month, the amount that you shift the current date depends on what day of the month it is now and the length of the current month.

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, then shift the result by n months:

DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL n MONTH)

For example, to find the first day of the previous and following months relative to a given date, n would be -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 |
+------------+-----------------------+------------------------+

Finding the last day of the month for a given reference date is more difficult, because months vary in length. However, the last day of the month is always the day before the first of the next month, and we know how to calculate the latter. Thus, for the general case, the last day of the month n months from a date can be determined using the following procedure:

1. Find the first day of the month
2. Shift the result by n+1 months
3. Shift back a day

The SQL expression to perform these operations look like this:

DATE_SUB(
 DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL n+1 MONTH),
 INTERVAL 1 DAY)

For example, to calculate the last day of the month for the previous, current, and following months relative to a given date, n would be -1, 0, and 1, and the expressions look like this:

mysql> SELECT d,
 -> DATE_SUB(
 -> DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL 0 MONTH),
 -> INTERVAL 1 DAY)
 -> AS 'last, prev. month',
 -> DATE_SUB(
 -> DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL 1 MONTH),
 -> INTERVAL 1 DAY)
 -> AS 'last, this month',
 -> DATE_SUB(
 -> DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL 2 MONTH),
 -> INTERVAL 1 DAY)
 -> AS 'last, following month'
 -> FROM date_val;
+------------+-------------------+------------------+-----------------------+
| d | last, prev. month | last, this month | last, following month |
+------------+-------------------+------------------+-----------------------+
| 1864-02-28 | 1864-01-31 | 1864-02-29 | 1864-03-31 |
| 1900-01-15 | 1899-12-31 | 1900-01-31 | 1900-02-28 |
| 1987-03-05 | 1987-02-28 | 1987-03-31 | 1987-04-30 |
| 1999-12-31 | 1999-11-30 | 1999-12-31 | 2000-01-31 |
| 2000-06-04 | 2000-05-31 | 2000-06-30 | 2000-07-31 |
+------------+-------------------+------------------+-----------------------+

The last day of the previous month is a special case for which the general expression can be simplified quite a bit:

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

The key feature of the general last-of-month expression is that it begins by finding the first-of-month value for the starting date. That gives you a useful point of reference, because you can always shift it forward or backward by month units to obtain another first-of-month value, which can in turn be shifted back a day to find a last-of-month value. If you determine last-of-month values by finding the last-of-month value for the starting date and then shifting that, you won't always get the correct result, because not all months have the same number of days. For example, an incorrect method for determining the last day of a given month is to find the last day of the previous month and add a month:

mysql> SELECT d,
 -> DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d) DAY),INTERVAL 1 MONTH)
 -> 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-28 |
| 1999-12-31 | 1999-12-30 |
| 2000-06-04 | 2000-06-30 |
+------------+---------------+

This fails because the day-of-month part of the resulting date may not be correct. In the rows for 1987-03-05 and 1999-12-31, the last day of the month has been calculated incorrectly. This will be true with the preceding formula for any month in which the month preceding the reference date has fewer days than the target month.