Finding First and Last Days of Months
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.
Solution
You can do this by date shifting.
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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.