Recipe 6.13. 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 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 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,INTERVAL n 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 | +------------+---------------+ 

See Also

Section 6.17 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.)