Q:

How to convert csv date format to into mysql db

At csv file, the date field is in such format:
2/9/2010 7:32
3/31/2011 21:20

I am using php + mysql for development.
I need to read it and store into mysql db.
final value to store in mysql should be format as below:
2010-02-09 07:32:00

What's the correct way of it?
Is mysql syntax alone can handle the conversion easily?

A:

Use the STR_TO_DATE() function.
Example
STR_TO_DATE('3/31/2011 21:20', '%c/%e/%Y %H:%i');

A:

I face the same issue and after little research this is how i resolved it-
LOAD DATA LOCAL INFILE 'D:/dataupload.csv' INTO TABLE table1
FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\r\n' (@d1,col2,col3,col4) 
SET col1 = date_format(str_to_date(@d1, **'%m/%d/%Y'**), **'%Y-%m-%d'**)
Details:

'%m/%d/%Y' - this is the format of date in my CSV file
'%Y-%m-%d' - this is the mysql format in which i want to convert my CSV field date while inserting data
col1 - is the actual column of my table (having date data type)
@d1 - is the dummy variable to use in set statement, you can take it any variable

