gpt4 book ai didi

mysql - 不正确的日期时间值 '0000-00-00 00:00:00' - Date_Sub() in Having

转载 作者:可可西里 更新时间:2023-11-01 07:11:21 24 4
gpt4 key购买 nike

所以我有一个看起来像这样的插页:

  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;

它从表中获取数据,执行一些计算,连接自身然后插入。失败并显示此消息:Incorrect DateTime Value '0000-00-00 00:00:00' .我已经缩小了范围,如果我从 having 子句中删除这一行 (DATE_ADD(MAX(COALESCE(InvoiceDate, NOW())) <= MIN(COALESCE(InvoiceDate, NOW())), INTERVAL -30 DAY))它有效。

事实上,当我奇怪地移除插入物时它起作用了。

选择没有返回任何东西,但它仍然失败。

这是为什么?谁能帮忙看看是什么原因?

最佳答案

第一个选项:

此警告可能是由于 SQL_MODE 引起的。

根据 mysql 文档“如果启用了 NO_ZERO_DATE 或 NO_ZERO_IN_DATE SQL 模式,则不允许使用零日期或部分日期。”。因此,这可能是您使用“0000-00-00 00:00:00”的 INSERT 失败的原因。

您可以通过执行此命令来检查您的 sql 模式:

SELECT @@sql_mode;

如果设置了任何 NO_ZERO_DATENO_ZERO_IN_DATE,那么您可以:

SET sql_mode = '';

第二个选项

另一个选项是由于 STRICT_TRANS_TABLES 模式而失败。正如 mysql 文档所说:

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

同样适用于日期时间。

因此您必须禁用严格模式如果禁用它不是一个选项 - 修改查询以使其不会返回无效的日期/日期时间结果

关于mysql - 不正确的日期时间值 '0000-00-00 00:00:00' - Date_Sub() in Having,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/40425568/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com