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
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(...,mydate,...);
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:
...
LINES TERMINATED BY '\n'
(...,STR_TO_DATE(mydate,'(%Y-%c-%e %H:%i:%S)') AS NewDateTime,...);