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