MySQL Cookbook

Finding the Length of a Month

Problem

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

Solution

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

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 |
+------------+---------------+

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.