Warning (Code 1411): Incorrect datetime value:

Question

Hi I am trying to add data to a table using mysql INFILE Command but getting the following error.

Warning (Code 1411): Incorrect datetime value: ''2015-05-15 15:57:46'' for function str_to_date
Warning (Code 1048): Column 'transaction_time' cannot be null

mysql -uroot test --show-warnings -vve "LOAD DATA INFILE '/Users/xyz/log2.tsv' into table logs_master fields terminated by '\t' enclosed by '' lines terminated by '\n' (@transaction_time, col2, col3, col4,...) SET updated_at = NOW(), transaction_time = STR_TO_DATE(@transaction_time, '%Y-%m-%d %H:%i:%s')"

CREATE TABLE `test_action_logs_master` (
  `transaction_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `col1` varchar(100) DEFAULT NULL,
  `col2` varchar(700) DEFAULT NULL,
  `col3` varchar(700) DEFAULT NULL,
  `col4` varchar(700) DEFAULT NULL,
  `col5` varchar(200) DEFAULT NULL,
  `col6` varchar(700) DEFAULT NULL,
  `col7` varchar(700) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `col3` (`col3`),
  KEY `col4` (`col4`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql version: Server version: 5.6.25 Machine: OS X(MAC) Yosemite

show variables like 'sql_mode'
    -> ;
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+
1 row in set (0.00 sec)

I have made the changes by looking at other threads on the issue and also looking at the link http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Somehow, it doesn't work and gives the listed error. The rest of the fields are sourced properly, just that the leading field which is a date time value is not getting sourced.

Is it possible that the value in the file actually has single quotes around it, such as `'2015-05-15 15:57:46'` rather than `2015-05-15 15:57:46`? As a general rule, I read files into a character-only staging table and then do the conversions in the database. It is often much easier to handle data conversions problems that way. — Gordon Linoff, Aug 03 '15 at 14:08
Hi Gordon, Thank you for the reply. Yes the date is enclosed in single quotes. I think the problem is with the format I am supplying to STR_TO_DATE. My Data Set is huge and using a temporary table would need a lot of time for the conversion. But yes I think the solution should work, since the same date with a insert statement works perfect. — Ankit Marothi, Aug 04 '15 at 06:43
@GordonLinoff: Thank You for pointing out, the issue was enclosing the date field in single quotes in the tsv files. When you remove the quotes it works!!! — Ankit Marothi, Aug 04 '15 at 07:35

score 1 · Answer 1 · answered Aug 04 '15 at 07:01

1

Your date field is enclosed in an extra pair of single quotes, so you need to adjust your format accordingly; this will get you your correct result (note the extra quotes):

SELECT STR_TO_DATE("'2015-05-15 15:57:46'", "'%Y-%m-%d %H:%i:%s'")

Your current statement is the following, and since the pattern doesn't match you are getting NULL:

SELECT STR_TO_DATE("'2015-05-15 15:57:46'", '%Y-%m-%d %H:%i:%s')

answered Aug 04 '15 at 07:01

Burhan Khalid

169,990
18
245
284

Thank you for helping! I think the issue is with the single quotes itself. The date should not be enclosed in single quotes in the tsv file. I am trying to source the files through the command line. I tried your solution through the command line by enclosing the literals in STR_TO_DATE in double quotes and then single quotes. But that wont work on the command line. It gives syntactical errors. – Ankit Marothi Aug 04 '15 at 07:43

Warning (Code 1411): Incorrect datetime value:

1 Answers1

Linked