Take the 2-minute tour ×

I'm trying to import a CSV file to a mysql database.

The CSV file contains (amongst other things) dates in the following format:

"2010-04-31 17:43:12"

My first approach was to use the following .sql script:

USE test;
LOAD DATA INFILE '/tmp/test.cvs'
replace INTO TABLE test_table

which doesn't work because the double quotes make the field "2010-04-31 17:43:12" a string. So i figured out i can convert it to DATETIME format using

select STR_TO_DATE("2010-04-31 17:43:12",'(%Y-%c-%e %H:%i:%S)') AS NewDateTime

That query works fine on it's own but I was wondering if there's a way of converting the string on the fly while importing. Something amongst the following:

(...,STR_TO_DATE(mydate,'(%Y-%c-%e %H:%i:%S)') AS NewDateTime,...);
share|improve this question
I think you mean CSV. –  Andrew Medico Oct 15 '10 at 3:36
whoops, sorry about that. –  kirbuchi Oct 15 '10 at 3:41
add comment

1 Answer

up vote 2 down vote accepted

You need to specify the column list, and then use the SET command to apply the STR_TO_DATE:

SET mydate = STR_TO_DATE(@date, '%Y-%c-%e %H:%i:%S')


share|improve this answer
thanks! that did the trick. –  kirbuchi Oct 15 '10 at 3:41
@kirbuchi: You're welcome. I'm surprised it doesn't come up more often –  OMG Ponies Oct 15 '10 at 3:42
add comment

Your Answer


