I am getting an incorrect date value on insert select

Question

I am wanting to do an insert select, but in phpmyadmin I keep getting an error #1292 - Incorrect date value: '' for column 'Date' at row 1 Now the select works on it's own without error. I only get the error when I add the INSERT statement eg this works

SELECT
    `SalesInvoice_id`,
    `Date`,
    IFNULL(`valueOriginal`+`VATOriginal`,0) as `Amount`
    FROM
    (SELECT 
        `salesinvoice`.`SalesInvoice_id`,
         `DatePaid` AS `Date`,
        ROUND(SUM((`Unit_Cost`*`Quantity`)),2) as `valueOriginal`,
        IF(`salesinvoice`.`VAT`=2,ROUND(SUM(((`Unit_Cost`)*`Quantity`)/100*`salesinvoice_products`.`VAT`),2),0) as `VATOriginal`,
         `InvoiceType`
        FROM `salesinvoice`
        LEFT JOIN `salesinvoice_products` ON `salesinvoice`.`SalesInvoice_id` = `salesinvoice_products`.`SalesInvoice_id`
          GROUP BY `salesinvoice`.`SalesInvoice_id`
    ) a
 WHERE `InvoiceType`!='3' && `Date`!=''

but when I do this

INSERT INTO `salesinvoice_payments` (`SalesInvoice_id`,`Date`,`Amount`)
SELECT
    `SalesInvoice_id`,
    `Date`,
    IFNULL(`valueOriginal`+`VATOriginal`,0) as `Amount`
    FROM
    (SELECT 
        `salesinvoice`.`SalesInvoice_id`,
         `DatePaid` AS `Date`,
        ROUND(SUM((`Unit_Cost`*`Quantity`)),2) as `valueOriginal`,
        IF(`salesinvoice`.`VAT`=2,ROUND(SUM(((`Unit_Cost`)*`Quantity`)/100*`salesinvoice_products`.`VAT`),2),0) as `VATOriginal`,
         `InvoiceType`
        FROM `salesinvoice`
        LEFT JOIN `salesinvoice_products` ON `salesinvoice`.`SalesInvoice_id` = `salesinvoice_products`.`SalesInvoice_id`
          GROUP BY `salesinvoice`.`SalesInvoice_id`
    ) a
 WHERE `InvoiceType`!='3' && `Date`!=''

I get the error. Ok Here is my schema

DROP TABLE IF EXISTS salesinvoice_payments;
DROP TABLE IF EXISTS salesinvoice;
DROP TABLE IF EXISTS salesinvoice_products;

CREATE TABLE salesinvoice_payments
    (`sipID` int(11) NOT NULL AUTO_INCREMENT,
    `salesinvoice_id` int,
    `Date` date,
    `Amount` int,
     PRIMARY KEY (`sipID`))
;

CREATE TABLE salesinvoice (
  `SalesInvoice_id` int(11) NOT NULL AUTO_INCREMENT,
  `VAT` tinyint(1) DEFAULT NULL,
  `DatePaid` date DEFAULT NULL,
  `InvoiceType` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`SalesInvoice_id`)
);

 CREATE TABLE salesinvoice_products (
  product_ID int(11) NOT NULL AUTO_INCREMENT,
  SalesInvoice_id int(11) DEFAULT NULL,
  Description varchar(255) DEFAULT NULL,
  Unit_Cost decimal(19, 2) DEFAULT NULL,
  Quantity int(11) DEFAULT NULL,
  VAT decimal(10, 1) DEFAULT NULL,
  PRIMARY KEY (product_ID)
);

INSERT INTO `salesinvoice`
    (`SalesInvoice_id`,`VAT`, `DatePaid`, `InvoiceType`)
VALUES
    (1,2,'2017-11-07',1),
    (2,2,'2017-11-07',1),
    (3,2,'2017-11-07',1),
    (4,2,'2017-11-07',1),
    (5,2,'2017-11-07',1),
    (6,2,'2017-11-07',1)
;

INSERT INTO `salesinvoice_products`
    (`SalesInvoice_id`, `Description`, `Unit_Cost`,`Quantity`,`VAT`)
VALUES
    (3,'Sausage',1.50,5,21),
    (3,'Cabbage',2.50,15,21),
    (4,'Herring',2.50,15,21),
    (4,'Red',2.50,15,21),
    (3,'Dongle',2.50,15,21),
    (2,'Thingy',2.50,15,21),
    (2,'Spaceship',2.50,15,21),
    (1,'Car',2.50,15,21),
    (1,'Alien',2.50,15,21)
;

INSERT INTO `salesinvoice_payments` (`SalesInvoice_id`,`Date`,`Amount`)
SELECT
    `SalesInvoice_id`,
    `Date`,
    IFNULL(`valueOriginal`+`VATOriginal`,0) as `Amount`
    FROM
    (SELECT 
        `salesinvoice`.`SalesInvoice_id`,
         `DatePaid` AS `Date`,
        ROUND(SUM((`Unit_Cost`*`Quantity`)),2) as `valueOriginal`,
        IF(`salesinvoice`.`VAT`=2,ROUND(SUM(((`Unit_Cost`)*`Quantity`)/100*`salesinvoice_products`.`VAT`),2),0) as `VATOriginal`,
         `InvoiceType`
        FROM `salesinvoice`
        LEFT JOIN `salesinvoice_products` ON `salesinvoice`.`SalesInvoice_id` = `salesinvoice_products`.`SalesInvoice_id`
          GROUP BY `salesinvoice`.`SalesInvoice_id`
    ) a
 WHERE `InvoiceType`!='3' && `Date`!=''

Ok and for the first time posting on stack overflow I am going to show my sqlfiddle. The select insert works fine in sqlfiddle,but not in phpmyadmin. However in phpmyadmin I get an error #1292 - Incorrect date value: '' for column 'Date' at row 1. SQLFIDDLE

For some reason the date is not being sent properly.

Answer 1

You get the same warning when you SELECT.

mysql> SELECT ... WHERE `InvoiceType`!='3' && `Date`!='';
+-----------------+------------+--------+
| SalesInvoice_id | Date       | Amount |
+-----------------+------------+--------+
|               1 | 2017-11-07 |  90.75 |
|               2 | 2017-11-07 |  90.75 |
|               3 | 2017-11-07 |  99.83 |
|               4 | 2017-11-07 |  90.75 |
|               5 | 2017-11-07 |   0.00 |
|               6 | 2017-11-07 |   0.00 |
+-----------------+------------+--------+
6 rows in set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1292 | Incorrect date value: '' for column 'Date' at row 1 |
+---------+------+-----------------------------------------------------+

Whereas there is no warning when we leave off that condition.

mysql> SELECT ... WHERE `InvoiceType`!='3';
+-----------------+------------+--------+
| SalesInvoice_id | Date       | Amount |
+-----------------+------------+--------+
|               1 | 2017-11-07 |  90.75 |
|               2 | 2017-11-07 |  90.75 |
|               3 | 2017-11-07 |  99.83 |
|               4 | 2017-11-07 |  90.75 |
|               5 | 2017-11-07 |   0.00 |
|               6 | 2017-11-07 |   0.00 |
+-----------------+------------+--------+
6 rows in set (0.01 sec)

So it's complaining about the comparison of a date column to '', not the insertion of a value into a table.

It makes no sense to compare a MySQL date to ''. A column with the DATE or DATETIME type cannot be an empty string. Invalid dates are converted to 0000-00-00, or if you use strict mode, they are an error. You can never get a date column that is ''.

You can get a date column that is NULL. That might have been what you were trying to test for. NULL and the empty string '' are different in standard SQL and in MySQL.

I guess you have Oracle experience, where NULL and the empty string are treated the same in many contexts (this behavior of Oracle is not standard SQL).

I recommend you use IS NOT NULL when you want to filter out NULL.

mysql> SELECT ... WHERE `InvoiceType`!='3' AND `Date` IS NOT NULL;
+-----------------+------------+--------+
| SalesInvoice_id | Date       | Amount |
+-----------------+------------+--------+
|               1 | 2017-11-07 |  90.75 |
|               2 | 2017-11-07 |  90.75 |
|               3 | 2017-11-07 |  99.83 |
|               4 | 2017-11-07 |  90.75 |
|               5 | 2017-11-07 |   0.00 |
|               6 | 2017-11-07 |   0.00 |
+-----------------+------------+--------+
6 rows in set (0.01 sec)