1

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)
mjkrause
  • 406
  • 1
  • 7
  • 14
  • Try `makedate` function. See this `SELECT YEAR(MAKEDATE(94,1));` returns `1994` – Allen King Aug 03 '17 at 03:30
  • MySQL is doing what it is designed to do. https://dev.mysql.com/doc/refman/5.5/en/two-digit-years.html You'll need to provide century. – Gurwinder Singh Aug 03 '17 at 03:36
  • I just had to do the same thing and decided it was easier as a one-time process to export the data to a text file and perform a regex text replace on it to insert "19" after the last hyphen, then re-import it into the DB. – BigBadMe May 01 '18 at 09:42

4 Answers4

2

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

Obsidian Age
  • 41,205
  • 10
  • 48
  • 71
  • *SQL times didn't exist before 1970* - Are you sure that is the reason? – Gurwinder Singh Aug 03 '17 at 03:32
  • Slightly improved the explanation for using times pre-1970. – Obsidian Age Aug 03 '17 at 03:37
  • Epoch value can be negative for dates prior to UNIX epoch. It's not like it couldn't supported. It's just how MySQL is designed. Considering the century is unknown, MySQL need to guess. In this case there are rules to guess - https://dev.mysql.com/doc/refman/5.5/en/two-digit-years.html – Gurwinder Singh Aug 03 '17 at 03:38
  • Dynamite @ObsidianAge and others! Thanks for all the links, very helpful. – mjkrause Aug 03 '17 at 16:07
0

Here's the complete SQL code to get what I wanted. Thanks to @ObsidianAge for helpful links making me aware of Unix time.

SELECT DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), interval TIMESTAMPDIFF(second,FROM_UNIXTIME(0), str_to_date(CONCAT('19', SUBSTRING('09-JAN-65',8,2), '-', SUBSTRING('09-JAN-65',4,3), '-', SUBSTRING('09-JAN-65',1,2)), '%Y-%b-%d')) second), '%Y-%m-%d');
mjkrause
  • 406
  • 1
  • 7
  • 14
0

I use checking if year values are higher than current year, if so, it's 1900+, if not, it's 2000+ :

SELECT SUBSTR('09-JAN-65',-2), IF( SUBSTR( '09-JAN-65',-2) > 19, 'date is 1900+', 'date is 2000+' ) FROM t;
Honza K.
  • 1
  • 3
-1

Try this:

SELECT Case when convert(SUBSTR('09-JAN-65',8,2), unsigned) < 70 
then str_to_date(CONCAT(SUBSTR('09-JAN-65',1,7), 19, SUBSTR('09-JAN-65',8,2)), '%d-%b-%Y') 
else str_to_date('09-JAN-65', '%d-%b-%y') END;

It works.

Sebastian D'Agostino
  • 1,575
  • 2
  • 27
  • 44
Nitin Vishwakarma
  • 1