| Bug #68865 | STR_TO_DATE with %Y does not appear to work as documented | ||
|---|---|---|---|
| Submitted: | 4 Apr 2013 12:17 | Modified: | 20 Apr 2013 19:25 |
| Reporter: | Yoseph Phillips | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[20 Apr 2013 19:25]
MySQL Verification Team
mysql is assuming the year is really 2021 in this case.
mysql> SELECT DATE_FORMAT(STR_TO_DATE('21/06/20', '%Y/%m/%d'), '%Y/%m/%d');
+--------------------------------------------------------------+
| DATE_FORMAT(STR_TO_DATE('21/06/20', '%Y/%m/%d'), '%Y/%m/%d') |
+--------------------------------------------------------------+
| 2021/06/20 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_FORMAT(STR_TO_DATE('21/06/20', '%y/%m/%d'), '%y/%m/%d');
+--------------------------------------------------------------+
| DATE_FORMAT(STR_TO_DATE('21/06/20', '%y/%m/%d'), '%y/%m/%d') |
+--------------------------------------------------------------+
| 21/06/20 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.6.12-debug |
+--------------+
1 row in set (0.00 sec)
http://dev.mysql.com/doc/refman/5.6/en/two-digit-years.html
Maybe it should return NULL with a warning if you forget to give a four digit date to %Y.
Description: SELECT STR_TO_DATE('21/06/20', '%Y/%m/%d') appears to function the same as SELECT STR_TO_DATE('21/06/20', '%y/%m/%d') in the it returns a non null value. According to the documentation it should be doing the inverse of DATE_FORMAT(). For values which are illegal according to this null should be returned, so when the year part does not contain 4 digits which DATE_FORMAT() would have produced I would expect SELECT STR_TO_DATE('21/06/20', '%Y/%m/%d') to return null. SELECT DATE_FORMAT(STR_TO_DATE('21/06/20', '%Y/%m/%d'), '%Y/%m/%d') returns something other than '21/06/20', which is not then inverse as documented, SELECT DATE_FORMAT(STR_TO_DATE('21/06/20', '%y/%m/%d'), '%y/%m/%d') is fine. How to repeat: SELECT STR_TO_DATE('21/06/20', '%Y/%m/%d')