how to convert datetime automatically from 00/00/0000 00:00:00 format to 0000-00-00 00:00:00 format on CSV

Question

I have the following csv

January,December,,,Blaize,Wildsights,Blaize,Wildsights,61,1,30,,,,,,,,,,10/09/2013 20:10:52,10/09/2013 20:11:05
January,December,,,Michael,Wildsights,Michael,Wildsights,61,2,31,,,,,,,,,,10/09/2013 20:11:03,10/09/2013 20:11:05
........
....
..

After importing it using phpmyadmin I saw that the timestamps have not been imported.

If it is not possible to keep the timestamps as 00/00/0000 00:00:00 how can I convert datetime automaticaly from 00/00/0000 00:00:00 format to 0000-00-00 00:00:00 format on CSV. I have thousand rows

I click "import" then, I click "choose file" I select the csv file ( first two colomns are shown above ). then I just click "Go". — Johnny, Jan 29 '17 at 11:38
@MikeNakis Agree, but phpmyadmin may not be perfect for all use cases. — Tim Biegeleisen, Jan 29 '17 at 11:44
phpmyadmin is just a tool I tried to use mysql front and heidi didn't work either. I guess I have to convert 10/09/2013 20:10:52 to 2013-09-10 20-10-52 how can I do it I have thousands rows — Johnny, Jan 29 '17 at 11:48

score 1 · Answer 1 · edited May 23 '17 at 11:54

Add a tempDate column of NVARCHAR type to your mySql database.
import your CSV telling phpmyadmin to import your problematic dates into the tempDate column instead of your actual date column. This will succeed, because tempDate is NVARCHAR, so it will accept anything.
Consult this: how to convert a string to date in mysql? on how to execute an UPDATE statement on your database to populate your actual date column from the tempDate column.
remove the tempDate column.

how to convert datetime automatically from 00/00/0000 00:00:00 format to 0000-00-00 00:00:00 format on CSV

1 Answers1