5.22 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:
Find the first day of the month
Shift the result by n+1
months
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.
| 
No comments:
Post a Comment