5.23 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.
|
