Database Reference
In-Depth Information
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 its 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 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 |
| 2017-03-16 | 2017-03-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 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 |
| 1999-12-31 | 1999-11-01 | 2000-01-01 |
| 2000-06-04 | 2000-05-01 | 2000-07-01 |
| 2017-03-16 | 2017-02-01 | 2017-04-01 |
+------------+-----------------------+------------------------+