Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function returns the day of the month for the date given. If the day for the date is beyond the end of the month (e.g., '2008-02-30'), the function returns NULL along with a warning that can be retrieved with SHOW WARNINGS. Here is an example:

SELECT DAYOFMONTH('2008-02-28') AS 'A Good Day',
DAYOFMONTH('2008-02-30') AS 'A Bad Day';

+------------+-----------+
| A Good Day | A Bad Day |
+------------+-----------+
|         28 |      NULL | 
+------------+-----------+
1 row in set, 1 warning (0.00 sec)

SHOW WARNINGS;

+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '2008-02-30' | 
+---------+------+--------------------------------------------------+

Prior to MySQL version 5.0.2, invalid dates such as this were permitted. The function would have returned 30 for a value of '2008-02-30'. If you wish to allow invalid dates, start your server with this line in your options file:

sql_mode = 'TRADITIONAL,ALLOW_INVALID_DATES'