警告（代码1411）：日期时间值不正确：

本文介绍了警告（代码1411）：日期时间值不正确：的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我试图使用mysql INFILE命令将数据添加到表中，但是出现以下错误。

 警告（代码1411）：datetime值不正确：''2015-05-15 15:57:46''for function str_to_date 
警告（代码1048）：列transaction_time不能为空

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.25Machine: 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 linkhttp://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.

解决方案

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')

这篇关于警告（代码1411）：日期时间值不正确：的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

MySQL

警告（代码1411）：日期时间值不正确：

问题描述