错误代码：1411。函数str_to_date的日期时间值不正确：'0000-00-00' [英] Error Code: 1411. Incorrect datetime value: '0000-00-00' for function str_to_date

问题描述

我正在尝试在插入之前通过CSV文件更改日期格式。我在插入之前创建了一个触发器，但它似乎不起作用。

 创建表项目（
 item_id int（ 11）非空AUTO_INCREMENT，
 price_list_id int（11）非空，
 sku varchar（20）非空，
描述varchar（45）默认为空，
成本浮动为非空DEFAULT'0'，
注释varchar（45）DEFAULT NULL，
 discount_factor浮点数DEFAULT'1'，
 start_date日期DEFAULT NULL，
 end_date日期DEFAULT NULL，
主键（item_id，sku），
键price_list_id_idx（price_list_id），
 CONSTRAINT price_list_id FOREIGN KEY（price_list_id）参考price_lists（price_list_id）启用时不执行任何操作AUTO_INCREMENT = 105193 DEFAULT CHARSET = utf8; 
  

这是插入之前的触发器

 创建触发器`pricelist`.ʻitems_BEFORE_INSERT`在对各行插入`items`之前
开始
 SET NEW.start_date = str_to_date（NEW.start_date，'％c /％e /％Y'）; 
 SET NEW.end_date = str_to_date（NEW.end_date，‘％c /％e /％Y’）; 
 
 END 
  

假设查询为： INSERT IGNORE INTO pricelist.items（price_list_id，sku，description，cost，start_date，end_date，notes）值（'15'，'2494-22'，'M12 DRILL / IMPACT COMBO KIT'，'129'，'4/25 / 2016'，'5/31/2016'，'CL6204'）;

我收到此错误：

 `错误代码：1411。日期时间值错误：函数str_to_date` 
 '0000-00-00' 

如果我这样做（没有IGNORE）：

 插入到pricelist.items（price_list_id，sku，描述，成本，start_date，end_date，注释）值（'15'，'2494-22'，'M12 DRILL / IMPACT COMBO KIT'，'129'，'4/25 / 2016， 5/31/2016， CL6204）； 
  

我明白了：

 错误代码：1292。错误的日期值：'4/25/2016'为第1行的$ start $ date列
  

虽然可行：

  SELECT str_to_date（'5/31/2016'，' ％c /％e /％Y'）； 
  

输出为：

  2016-05-31 
  

任何帮助将不胜感激！谢谢。

解决方案

您正在获取此错误，因为您试图插入日期列格式错误。 MySQL文档列出了格式可以接受的：

MySQL可以识别以下格式的DATE值：

作为'YYYY-MM的字符串-DD或 YY-MM-DD格式。允许使用宽松语法：任何标点符号都可以用作日期部分之间的分隔符。例如， 2012-12-31， 2012/12/31， 2012 ^ 12 ^ 31和 2012 @ 12 @ 31是等效的。

As

作为带有YYYYMMDD或YYMMDD格式的数字的，没有分隔符的，'YYYYMMDD'或'YYMMDD'格式的字符串。

但是您的日期格式为 MM / DD / YYYY ，例如 2016/4/25 ，因此将无法正常运行。看来您试图在插入之前使用触发器来更正格式。但是，MySQL会在触发之前甚至在触发前检查 的格式。

如果必须以这种格式插入日期数据，则应使用 VARCHAR 类型，然后再调用 STR_TO_DATE 。或者，您应该清理日期格式以匹配可接受的格式之一。

I'm trying to change the format of a date from a CSV file before insert. I created a trigger BEFORE INSERT but it doesn't seem to work.

CREATE TABLE items (
  item_id int(11) NOT NULL AUTO_INCREMENT,
  price_list_id int(11) NOT NULL,
  sku varchar(20) NOT NULL,
  description varchar(45) DEFAULT NULL,
  cost float NOT NULL DEFAULT '0',
  notes varchar(45) DEFAULT NULL,
  discount_factor float DEFAULT '1',
  start_date date DEFAULT NULL,
  end_date date DEFAULT NULL,
  PRIMARY KEY (item_id,sku),
  KEY price_list_id_idx (price_list_id),
  CONSTRAINT price_list_id FOREIGN KEY (price_list_id) REFERENCES price_lists (price_list_id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=105193 DEFAULT CHARSET=utf8;

This is my trigger BEFORE INSERT

CREATE TRIGGER `pricelist`.`items_BEFORE_INSERT` BEFORE INSERT ON `items` FOR EACH ROW
BEGIN
    SET NEW.start_date = str_to_date(NEW.start_date, '%c/%e/%Y');
    SET NEW.end_date = str_to_date(NEW.end_date, '%c/%e/%Y');

END

Let say the query is : INSERT IGNORE INTO pricelist.items (price_list_id, sku, description, cost, start_date, end_date, notes) VALUES ('15', '2494-22', 'M12 DRILL/IMPACT COMBO KIT', '129', '4/25/2016', '5/31/2016', 'CL6204');

I get this error :

`Error Code: 1411. Incorrect datetime value: '0000-00-00' for function str_to_date`

If i do (without IGNORE):

INSERT INTO pricelist.items (price_list_id, sku, description, cost, start_date, end_date, notes) VALUES ('15', '2494-22', 'M12 DRILL/IMPACT COMBO KIT', '129', '4/25/2016', '5/31/2016', 'CL6204');

I get this :

Error Code: 1292. Incorrect date value: '4/25/2016' for column 'start_date' at row 1

Although this works :

SELECT str_to_date('5/31/2016', '%c/%e/%Y');

The output is :

2016-05-31

Any help would be appreciated! Thanks.

解决方案

You are gettting this error because you are trying to insert a date column which is of the wrong format. The MySQL documentation lists the formats which are acceptable:

MySQL recognizes DATE values in these formats:

As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A "relaxed" syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.

As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date.

As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date.

But your date has the format MM/DD/YYYY, e.g. 4/25/2016, so this won't work. It appears you were attempting to use a trigger to correct the format before the insert. However, MySQL checks the formatting before the trigger is even hit.

If you must insert date data with this format then you should do so using a VARCHAR type and then call STR_TO_DATE afterwards. Or, you should clean up the formatting of your dates to match one of the acceptable formats.

这篇关于错误代码：1411。函数str_to_date的日期时间值不正确：'0000-00-00'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！