How to convert csv date format to into mysql db

Question

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?

Answer 1

Use the STR_TO_DATE() function.

Example

STR_TO_DATE('3/31/2011 21:20', '%c/%e/%Y %H:%i');

Answer 2

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:

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

Answer 3

I had the same problem (with DATE) and another solution, is to use the native mysql format YYYYMMDD ie 20120209. I haven't tried with DATETIME but I guess YYYYMMDDhhmmss will work.