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?

Community · Accepted Answer · 2020-06-20 09:12:55Z

6

Use the STR_TO_DATE() function.

Example

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Apr 11, 2011 at 3:34

alex

492k205 gold badges891 silver badges992 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hendry Tanaka Over a year ago

How about this? Any help please? Thanks

Mo. · Accepted Answer · 2015-12-07 14:29:17Z

4

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

edited Dec 7, 2015 at 14:29

Mo.

43.2k38 gold badges91 silver badges134 bronze badges

answered Jul 4, 2013 at 8:46

Amit Baderia

4,9524 gold badges31 silver badges20 bronze badges

Comments

mb14 · Accepted Answer · 2012-05-30 11:11:33Z

0

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.

answered May 30, 2012 at 11:11

mb14

22.7k9 gold badges63 silver badges108 bronze badges

Collectives™ on Stack Overflow

How to convert csv date format to into mysql db

3 Answers 3

Example

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Example

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related