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