Bug #15446 Incorrect Date Formatting
Submitted: 2 Dec 2005 20:37 Modified: 2 Dec 2005 20:45
Reporter: Josh Parks Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7 OS:Microsoft Windows (Win XP)
Assigned to: CPU Architecture:Any

[2 Dec 2005 20:37] Josh Parks
Description:
It is possible to enter an invalid date format into a datetype column on a DB record if the current date is the 1st of the month.  It will not subtract a date from the CURDATE() function properly.

How to repeat:
Set computer Date to the 1st of the month.

update into tblname (DateColumnName) values (CURDATE() - 1)

The date in that record will now be the zeroeth day of the month rather than the last day of the previous month.

Ex:  If curdate = "2005-12-01" the above query will change the date to "2005-12-00" rather than erroring out or setting the date to "2005-11-30".

Suggested fix:
Give Error if it returns an invalid date format rather than accepting the data value.
[2 Dec 2005 20:45] Valeriy Kravchuk
Thank you for a problem report. Sorry, but the behaviour you described is known and intended. Use separate functions to get correct results:

mysql> select curdate() - 1;
+---------------+
| curdate() - 1 |
+---------------+
|      20051201 |
+---------------+
1 row in set (0.00 sec)

mysql> select curdate() - 2;
+---------------+
| curdate() - 2 |
+---------------+
|      20051200 | <--- wrong
+---------------+
1 row in set (0.00 sec)

mysql> select date_sub(curdate(), interval 2 day);
+-------------------------------------+
| date_sub(curdate(), interval 2 day) |
+-------------------------------------+
| 2005-11-30                          | <--- right!
+-------------------------------------+
1 row in set (0.01 sec)

Read the manual, http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html, for the details.