Importing a CSV to MySQL with different date format

Question

I'm doing my first CSV import into MySQL and noticed that the date in the CSV has the format 31-Jan-2011. How can I convert this to 2011-01-31 so I can place it in the DATE datatype? The first thing which came to mind is let PHP do the conversion then insert it into a 2nd table but I'm guessing that's...not right.

Answer 1

You can replace format during importing data from the CSV file, for example -

LOAD DATA INFILE 'file_name.csv' INTO TABLE table_name FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' (id, column2, column3, @date_time_variable) -- read one of the field to variable SET date_time_column = STR_TO_DATE(@date_time_variable, '%d-%b-%Y'); -- format this date-time variable 

It will format the string like '31-Jan-2011' to a correct DATETIME data type.

More information here - LOAD DATA INFILE Syntax.

Answer 2

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d'); +------------------------------------------------+ | DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d') | +------------------------------------------------+ | 2009-10-04                                     |  +------------------------------------------------+ 1 row in set (0.00 sec)