Database Reference
In-Depth Information
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 |
| 1999-12-31 | 1999-12-31 |
| 2000-06-04 | 2000-06-30 |
| 2017-03-16 | 2017-03-31 |
+------------+---------------+
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, 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 |
| 1999-12-31 | 1999-11-30 | 2000-01-31 |
| 2000-06-04 | 2000-05-31 | 2000-07-31 |
| 2017-03-16 | 2017-02-28 | 2017-04-30 |
+------------+------------------------+-------------------------+
To find the length of a month in days, determine the date of its last day with
LAST_DAY()
, 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 |
| 1999-12-31 | 31 |
| 2000-06-04 | 30 |
| 2017-03-16 | 31 |
+------------+---------------+
