Search code examples
mysqly2k

MySQL CSV Imported Year Dates as 0018 Invalid Date

Getting an error when trying to correct the year portion of imported dates.

CSV Date Column Values were formatted

07/21/18 instead of 07/21/2018

This caused MySql to Insert Date as 07/21/0018

I was under the impression that year values in the range 00-69 were converted to 2000-2069 as stated in the documentation.

Any way to fix this? I've tried quite a few statements with no luck...

Any help appreciated

Solution

  • Assuming you want to just update the data in place, and it is a column of Date, DateTime or Timestamp types, you could do this:

    UPDATE table SET date = date + INTERVAL 2000 YEAR WHERE YEAR(date) < 70