0

DateTime Format in .csv file: 

4/18/2017 22:15

The file is being read like this:

load data local infile 'C:/somedirectory/somefile.csv'
into table InFile
fields terminated by ',' lines terminated by '\n' ignore 1 lines;

Loading this into a predefined table with the date-time column type datetime fails.

Loading this into a predefined table with the date-time column type varchar(25) works.

How do I change the order of the day/month/year in the table (not just dynamically) so I can do this:

ALTER TABLE InFile MODIFY COLUMN date-time datetime;

(After this question was answered it got marked as a possible duplicate. While the other answer is close, this question is about date AND time, and the answer here shows how to include the time part. It may not be obvious to new MySQL users that you can add the ' ' and ':' in the middle of the % letters.)

  • Possible duplicate of [How to change string date to MySQL date format at time of import of CSV using MySQL's LOAD DATA LOCAL INFILE](https://stackoverflow.com/questions/2238611/how-to-change-string-date-to-mysql-date-format-at-time-of-import-of-csv-using-my) – miken32 Sep 27 '17 at 23:22

1 Answers1

0

You need to use the STR_TO_DATE function to parse the datetime if it's not in the format that MySQL understands by default.

LOAD DATA LOCAL INFILE 'filename'
INTO TABLE InFile
fields terminated by ',' lines terminated by '\n' ignore 1 lines
(col1, col2, @datetime, col3, col4)
set `date-time` = STR_TO_DATE(@datetime, '%m/%d/%Y %H:%i')

AFAIK, there's no way to change a column so it automatically parses dates differently.

Barmar
  • 741,623
  • 53
  • 500
  • 612