Finding the Length of a Month

5.23.1 Problem

You want to know how many days there are in a month.

5.23.2 Solution

Determine the date of its last day, then extract the day-of-month component from the result.

5.23.3 Discussion

To determine the number of days for the month in which a given date occurs, calculate the date for the last day of the month as shown in the previous section, then extract the DAYOFMONTH( ) value from the result:

mysql> SELECT d, -> DAYOFMONTH(DATE_SUB( -> DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL 1 MONTH), -> INTERVAL 1 DAY)) -> 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 | +------------+---------------+

5.23.4 See Also

Recipe 5.28 later in this chapter discusses another way to calculate month lengths. Chapter 10 discusses leap year calculations in the context of date validation.