23

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.

enchance
  • 29,075
  • 35
  • 87
  • 127
  • Looks like someone already answered this over at: http://stackoverflow.com/questions/5616494/how-to-convert-csv-date-format-to-into-mysql-db. I'm posting it in case someone else has this question. – enchance Nov 17 '11 at 06:39

3 Answers3

52

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.

Devart
  • 119,203
  • 23
  • 166
  • 186
1
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)
Rush
  • 740
  • 4
  • 13
-2

If the file isn't too big, load the CSV into Excel and use the formatting commands there to change the date representation.

Fagan Walker
  • 33
  • 1
  • Your answer doesn't really help with the SQL question. Furthermore, Excel isn't really a too that is easy to embed in a batch process. – Thomas Baruchel Dec 19 '15 at 22:41
  • This worked for me, simply changed the date format in excel for the column to "yyyy-mm-dd", saved as csv and imported. – John Little Jan 17 '17 at 11:10