MySQL in a Nutshell, 2nd Edition

Name

DAYOFMONTH()

Synopsis

DAYOFMONTH(date)

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'