CODEWITHSUNDEEP
mysqlcsv
Anil
Anil

Reputation: 2465

Date conversion from csv to mysql

I'm trying to import (through phpMyadmin import option) a csv file to mysql table. The CSV file has a column date with the format mm/dd/yyyy (10/21/2009) and the column in mysql table has the date format yyyy-mm-dd. I've tried to change formats in csv but after importing to mysql the date column shows 0000-00-00 or some other wrong dates. Which is the correct date format to be stored in the csv file so that it shows the correct date after importing?

Thanks.

Upvotes: 0

Views: 174

Answers (1)

Jason McCreary
Jason McCreary

Reputation: 73031

  • Use a date column for the imported data (i.e. varchar)
  • Add a column for the new date column (i.e. Date)
  • Use STR_TO_DATE() to convert.
  • Drop old date column (optional)

In your case:

UPDATE your_table SET new_date_col = STR_TO_DATE(old_date_col, '%m/%d/%Y')

Adjust the original format to your needs.

Upvotes: 2

Related Questions