您正在获取此错误，因为您试图插入日期列格式错误。 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.