Incorrect DateTime Value '0000-00-00 00:00:00' - Date_Sub() in Having
Solution 1
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
Solution 2
Put this code before query
SET sql_mode = '';
or
go to /etc/mysql/my.cnf
& comment out STRICT_TRANS_TABLES
useful note SQL STRICT MODE
Andrew Kilburn
Updated on July 26, 2022Comments
-
Andrew Kilburn almost 2 years
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?