mysqldateformatting2-digit-year

how to use str_to_date (MySQL) to parse two-digit year correctly

I'm using MySQL and I need to import a table that has DOBs from the last century. So the person was born in 1965, but the two-digit year format is used in the string. Any idea how to get this right?

mysql> select str_to_date('09-JAN-65', '%d-%b-%y');
+--------------------------------------+
| str_to_date('09-JAN-65', '%d-%b-%y') |
+--------------------------------------+
| 2065-01-09                           |
+--------------------------------------+
1 row in set (0.01 sec)
Solution

  • You can't use str_to_date() with a year of 1965, as the first of January 1970 is the so called Unix epoch, when we started using UNIX time.

    Instead, use DATE_FORMAT from an epoch:

    SELECT DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), interval [timestamp] second), '%Y-%m-%d');

    In your example, this would be:

    SELECT DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), interval -157075200 second), '%Y-%m-%d');

    This can be seen working at SQLFiddle here.

    More information regarding epochs can be found here, and an epoch converter can be found here.

    Hope this helps! :)