Bug #45801 str_to_date formats non-dates as dates instead of returning a null value
Submitted: 27 Jun 2009 16:11 Modified: 27 Jun 2009 17:40
Reporter: Michael McLaughlin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.34 OS:Any
Assigned to: CPU Architecture:Any
Tags: STR_TO_DATE
Triage: Triaged: D2 (Serious)

[27 Jun 2009 16:11] Michael McLaughlin
Description:
The str_to_date converts non-date strings to dates when they have a:

Range of day values from 30 to 31 for February in a leap year.
Range of day values from 29 to 31 for February in a non-leap year.
Range of day values of 31 in all 30 day months.

Shouldn't they return a null, like the 32nd or beyond day of any month?

While its unlikely somebody would type in the wrong date, this can occur for something as simple as this date calculation when the utc_date() returns something like 20090627:

mysql> select str_to_date(utc_date() + 4,'%Y%m%d');

It would return 2009-06-31.

Naturally, the better way to perform this date calculation is:

mysql> select adddate(utc_date(),INTERVAL 4 DAY);

Unfortunately, not everybody uses the best way. It strikes me the the str_to_date() function isn't working as desired.

How to repeat:
mysql> SELECT str_to_date('20090229','%Y%m%d');
+----------------------------------+
| str_to_date('20090229','%Y%m%d') |
+----------------------------------+
| 2009-02-29                       |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT str_to_date('20090231','%Y%m%d');
+----------------------------------+
| str_to_date('20090231','%Y%m%d') |
+----------------------------------+
| 2009-02-31                       |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT str_to_date('20090631','%Y%m%d');
+----------------------------------+
| str_to_date('20090631','%Y%m%d') |
+----------------------------------+
| 2009-06-31                       |
+----------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Have the str_to_date return null for all non-date values like it does for the 32nd or beyond day of any month.

mysql> SELECT str_to_date('20090532','%Y%m%d');
+----------------------------------+
| str_to_date('20090532','%Y%m%d') |
+----------------------------------+
| NULL                             |
+----------------------------------+
1 row in set, 1 warning (0.00 sec)
[27 Jun 2009 17:40] Miguel Solorzano
Thank you for the bug report.