October 2002
Intermediate to advanced
1024 pages
27h 26m
English
You want to know how many days there are in a month.
Determine the date of its last day, then extract the day-of-month component from the result.
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 | +------------+---------------+
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.