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)
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)