Shulou(Shulou.com)05/31 Report--

Xiaobian to share with you mysql load error Incorrect datetime value how to do, I believe most people still do not know how, so share this article for your reference, I hope you read this article after a lot of gains, let us go to understand it together!

There is a requirement to pour data from oracle into mysql,

Method 1: Use spool to generate a txt file, and then load it into mysql

oracle source: spool configuration

[oracle11@S248 ~]$ cat spool_bak.sql

set colsep '|'

SET feedback off

SET newpage none

SET pagesize 50000

SET linesize 20000

SET verify off

SET pagesize 0

SET term off

SET trims ON

SET heading off

SET trimspool ON

SET trimout ON

SET timing off

SET verify off

spool /oracle/spool1.txt

select swebid,dstart from liuwenhe.fukuandate where dstart is not null and swebid is not null ;

spool off

EOF

Run spool file to generate txt file

SQL> @spool_bak.sql

Send the generated txt file to mysql server.

Then load it into MySQL.

mysql> LOAD DATA INFILE 'C:\\Users\\manet\\Desktop\\spool.txt' INTO TABLE fukuandate FIELDS TERMINATED BY "|"

Unfortunately, it was reported incorrectly:

Incorrect datetime value: '15-SEP-15' for column 'DSTART' at row 1

It is obviously a problem with the date format. Check out the contents of the txt file exported:

[oracle11@S248 ~]$ cat spool1.txt

403965|15-SEP-15

442917|01-AUG-16

However, mysql's default date format is y-mm-dd

mysql> show variables like 'datetime_format';####View default format of mysql

+-----------------+-------------------+

| Variable_name | Value |

+-----------------+-------------------+

| datetime_format | %Y-%m-%d %H:%i:%s |

+-----------------+-------------------+

1 row in set (0.00 sec)

mysql> SELECT NOW() FROM DUAL;

+---------------------+

| NOW() |

+---------------------+

| 2017-03-03 13:32:18 |

+---------------------+

1 row in set (0.00 sec)

So guess to change the date in the data to y-mm-dd form, modify oraclespool.sql script, change the date format to y-mm-dd form.

[oracle11@S248 ~]$ cat spool.sql

set colsep '|'

SET feedback off

SET newpage none

SET pagesize 50000

SET linesize 20000

SET verify off

SET pagesize 0

SET term off

SET trims ON

SET heading off

SET trimspool ON

SET trimout ON

SET timing off

SET verify off

spool /oracle/spool.txt

select swebid,to_char(dstart,'yyyy-mm-dd') from liuwenhe.fukuandate where dstart is not null and swebid is not null;

spool off

EOF

Then re-execute spool.sql script, newly generated txt file, load it again, successful.

mysql> LOAD DATA INFILE 'C:\\Users\\manet\\Desktop\\spool.txt' INTO TABLE fukuandate FIELDS TERMINATED BY "|"

A total of 73506 lines were affected

Method 2: Note that this method is only suitable for small amounts of data, because if the amount of data is large, you cannot edit the sql file. Use plsql developer tool to export sql file, and then modify sql file, pour into mysql.

In plsql developer tool, find the table to export on the left, then-right-click-select export data, and then select the path, as shown below:

Then edit the exported sql file so that it executes correctly in mysql, replacing to_date with STR_TO_DATE

insert into FUKUANDATE (SWEBID, DSTART)

values (403965, to_date('15-09-2015', 'dd-mm-yyyy'));

Amend to read

INSERT INTO FUKUANDATE (SWEBID, DSTART) VALUES (403965,STR_TO_DATE('15-09-2015','%d-%m-%Y'));

Then execute it,

The above is "mysql load error Incorrect datetime value how to do" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.