Bug #15446 Incorrect Date Formatting

• Submitted: 2 Dec 2005 20:37
• Modified: 2 Dec 2005 20:45
• Reporter: Josh Parks
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.7
• OS: Windows (Win XP)
• 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.