CODEWITHSUNDEEP
mysqldatecsv
i need help
i need help

Reputation: 2386

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?

Upvotes: 4

Views: 7756

Answers (3)

Amit Baderia
Amit Baderia

Reputation: 4892

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

Upvotes: 4

mb14
mb14

Reputation: 22636

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.

Upvotes: 0

alex
alex

Reputation: 490527

Use the STR_TO_DATE() function.

Example

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

Upvotes: 6

Related Questions