6

I want to upload my csv file that contains yyyy-mm-ddThh:mm:ss.sssZ data.

When I set DATETIME type in MySQL,

I got error code 1292.

MySQL How can I upload yyyy-mm-ddThh:mm:ss.sssZ type successfully?

enter image description here

1000111
  • 13,169
  • 2
  • 28
  • 37
SUN
  • 73
  • 1
  • 1
  • 5
  • Could you please share some of your real data? You need to insert the data in a `varchar` type column. Then you need to update the string dates into a valid date format. Only then changing the datatype to `date` would make it a success – 1000111 Dec 08 '16 at 15:03
  • I added picture of my data(csv file). Then, how can I update the string dates into valid date format with uploaded data in DB? – SUN Dec 08 '16 at 15:11

1 Answers1

4

First you need to convert your string dates into a valid date time format (yyyy-mm-dd hh:mm:ss).

With the help of STR_TO_DATE() and DATE_FORMAT() functions you can convert those date strings into the above desired format.

Now you can safely change/modify the datatype to timestamp/datetime.

Here's a demonstration:

SQL FIDDLE DEMO

Convert string dates into valid date format:

Create table yourtable(
   id INT primary key AUTO_INCREMENT,
   start varchar(50)
);

INSERT INTO yourtable(start) VALUES('1901-02-03T05:30:00.000Z');

UPDATE yourtable
SET start = DATE_FORMAT(STR_TO_DATE(start,'%Y-%m-%dT%H:%i:%s.000Z'),'%Y-%m-%d %H:%i:%s');

Change the datatype:

ALTER TABLE yourtable MODIFY COLUMN start datetime;
1000111
  • 13,169
  • 2
  • 28
  • 37
  • i'm appreciate for your help!! – SUN Dec 08 '16 at 22:11
  • You can mark the answer as Accepted so that others find it useful. http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – 1000111 Dec 09 '16 at 08:28