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:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any

[4 Apr 2013 12:17] Yoseph Phillips
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')
[20 Apr 2013 19:25] Shane Bester
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.