Andrew Kilburn Andrew Kilburn - 1 month ago 7
MySQL Question

Incorrect DateTime Value '0000-00-00 00:00:00' - Date_Sub() in Having

So I have an insert which looks like this:

INSERT INTO TempDupeData (Sys_InvoiceID, DupeSetID, Netted, InvoiceNo, InvoiceDate, Sys_SupplierID, SuppInvNo, NetAmount, VATAmount, GrossAmount, PayDate, PayRef,PayRefNo, PayType,
PayAmount, Curr, GBPNetAmount, GBPVATAmount, GBPGrossAmount, NetAmountAbs, GrossAmountAbs, VATAmountAbs, DocType, SuppInvNoNums, VATPerc, VATA, VATB, VATC,
VATD, VATE, VATPotentialClaim, ClaimStatus, SuppInvNoSubst, SuppInvNoSubstFlag, DupeSubstGross, DupeSubstNet, SuppInvNoCharMap, SuppInvNoCharMapFlag, SeqErrFlag)

SELECT
FK_SysInvoiceID,
CONCAT(CTE.NetAmountAbs, CTE.AccountNumber),
Netted,
InvoiceNo,
InvoiceDate,
I.FK_SupplierID,
SuppInvNo,
NetAmount,
VATAmount,
GrossAmount,
PayDate,
PayRef,
PayRefNo,
PayType,
PayAmount,
Curr,
GBPNetAmount,
GBPVATAmount,
GBPGrossAmount,
CTE.NetAmountAbs,
GrossAmountAbs,
VATAmountAbs,
DocType,
SuppInvNoNums,
VATPerc,
VATA,
VATB,
VATC,
VATD,
VATE,
VATPotentialClaim,
ClaimStatus,
SuppInvNoSubst,
SuppInvNoSubstFlag,
DupeSubstGrs,
DupeSubstNet,
SuppInvNoCharMap,
SuppInvNoCharMapFlag,
SeqErrFlag
FROM (SELECT
FK_SupplierID,
AccountNumber,
NetAmountAbs,
CASE WHEN MIN(NetAmountAbs) < SUM(NetAmount) THEN 0 ELSE -1 END AS Netted

FROM invoice
WHERE NetAmountAbs >= 500 and InvoiceDate IS NOT null
GROUP BY FK_SupplierID,
NetAmountAbs
HAVING COUNT(*) > 1
AND ((SUM(CASE WHEN NetAmount >= 0 THEN 1 ELSE 0 END)) > 1)
AND (MAX(SuppInvNoNums) != MIN(SuppInvNoNums))
AND (MIN(InvoiceDate) != MAX(InvoiceDate))
AND (DATE_SUB(MAX(COALESCE(InvoiceDate, NOW())) <= MIN(COALESCE(InvoiceDate, NOW())), INTERVAL 30 DAY))
AND (MAX(GrossAmountAbs) != MIN(GrossAmountAbs))) CTE
INNER JOIN invoice I
ON CTE.NetAmountAbs = I.NetAmountAbs
AND CTE.FK_SupplierID = I.FK_SupplierID
ORDER BY CTE.NetAmountAbs DESC, CTE.FK_SupplierID;


It takes from a table, performs some calculations, joins on itself and then inserts. It failed with this message:
Incorrect DateTime Value '0000-00-00 00:00:00'
. I have narrowed it down and if I remove this line from the having clause
(DATE_ADD(MAX(COALESCE(InvoiceDate, NOW())) <= MIN(COALESCE(InvoiceDate, NOW())), INTERVAL -30 DAY))
it works.

In fact, it works when I remove the insert strangely.

The select doesn't return anything yet it still fails.

Why is this? Can anyone help find out why?

Answer

First option:

This warning probably could be due to the SQL_MODE.

According to mysql documentation "If the NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode is enabled, zero dates or part of dates are disallowed.". So this may be the cause your INSERT with '0000-00-00 00:00:00' fails.

You can check your sql mode by executing this:

SELECT @@sql_mode;

and if any of the NO_ZERO_DATE or NO_ZERO_IN_DATE are set, then you can just:

SET sql_mode = '';

Second option

The other option is it is failing because of the STRICT_TRANS_TABLES mode. As mysql documentation say:

Strict mode affects whether the server permits '0000-00-00' as a valid date: If strict mode is not enabled, '0000-00-00' is permitted and inserts produce no warning. If strict mode is enabled, '0000-00-00' is not permitted and inserts produce an error, unless IGNORE is given as well. For INSERT IGNORE and UPDATE IGNORE, '0000-00-00' is permitted and inserts produce a warning

The same is valid for datetime.

So you have to either disable STRICT MODE OR if disabling it is not an option - modify the query so it doesn't return invalid date/datetime result