ProblemGiven 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. SolutionTo 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( ). DiscussionSometimes 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),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 | | 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,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 | | 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 | +------------+---------------+ See AlsoSection 6.17 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.) |