how to make phpMyAdmin import datetime correctly from csv?

Question

I've been provided a csv file which contains an export of a client's database table. Two of the columns are dates, and in the file they're formatted as mm/dd/yyyy.

ID | ActivateDate
-----------------
1  | 05/22/2010
2  | 10/01/2010

Our mySQL database that I need to import them into has those columns defined as datetime, with a default value of null. When I use the import function in phpMyAdmin, it's setting all the date columns in the imported records to 0000-00-00 00:00:00, regardless of whether there's any value in the import file.

Can anyone tell me what I need to do to get the ActivateDate column in the database to be set to 2010-05-22 00:00:00 instead of 0000-00-00 00:00:00?

Answer 1

If at all possible, I'd import those values into a varchar column fake_column first, and then push them over into the real column real_columnusing STR_TO_DATE.

UPDATE tablename SET real_column = STR_TO_DATE(fake_column, '%m/%d/%Y');

Reference on how to build the format string

Answer 2

If you have the file in a CSV format open it up into excel.

1. Right click the column heading that contains the dates.
2. Select Format Cells
3. Click the "Custom" category
4. Paste "yyyy-mm-dd h:mm:ss" in the input box.
5. Save the document.
6. Import into table using phpMyAdmin