Error Code: 1411. Incorrect datetime value: '0000-00-00' for function str_to_date
<p>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.</p>

<pre><code>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;
</code></pre>

<p>This is my trigger BEFORE INSERT    </p>

<pre><code>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
</code></pre>

<p>Let say the query is : <code>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');</code></p>

<p>I get this error : </p>

<pre><code>`Error Code: 1411. Incorrect datetime value: '0000-00-00' for function str_to_date`
</code></pre>

<p>If i do (without IGNORE):</p>

<pre><code>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');
</code></pre>

<p>I get this :</p>

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

<p>Although this works :</p>

<pre><code>SELECT str_to_date('5/31/2016', '%c/%e/%Y');
</code></pre>

<p>The output is :</p>

<pre><code>2016-05-31
</code></pre>

<p>Any help would be appreciated! Thanks.</p>