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

Question

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.

I'm not sure but I think it has something to do with your default null on your dates in the table. — Richard Barker, Jun 15 '16 at 01:05
I think you're sending in the wrong value to str_to_date. It looks like you're using the row currently being inserted and not the values passed to the insert statement... But I could be wrong. — Richard Barker, Jun 15 '16 at 01:10
`the row currently be inserted and not the values passed to the insert statement` ... aren't these the same thing? — Tim Biegeleisen, Jun 15 '16 at 01:12
I read the documentation and that's what they were doing. New.columnname is the value that is going to be inserted in the "columnname" — GeniusMile, Jun 15 '16 at 01:14

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 15 '16 at 01:18

Tim Biegeleisen

387,723
20
200
263

Seems to be a correct answer, I thought maybe BEFORE INSERT Trigger would have been a solution.. I'll try to find another solution, thanks. – GeniusMile Jun 15 '16 at 01:21
So the select statement at the end of the question fails then? – Richard Barker Jun 15 '16 at 01:21
You were clever to try to use `BEFORE INSERT` but MySQL still has a restriction on the format of `date`. – Tim Biegeleisen Jun 15 '16 at 01:22
@TimBiegeleisen I think I'll change it do varchar and call it a day. :P – GeniusMile Jun 15 '16 at 01:24
1

@TimBiegeleisen thanks a lot! deleted the old row, created new one's with varchar and my trigger does the right formatting! – GeniusMile Jun 15 '16 at 01:32

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

1 Answers1

Linked