不正确的日期时间值 '0000-00-00 00:00:00' - Date_Sub() 在有 [英] 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;

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

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?

推荐答案

第一个选项:

这个警告可能是由于 SQL_MODE.

This warning probably could be due to the SQL_MODE.

根据 mysql 文档如果启用了 NO_ZERO_DATE 或 NO_ZERO_IN_DATE SQL 模式，则不允许零日期或部分日期.".所以这可能是你的 INSERT with '0000-00-00 00:00:00' 失败的原因.

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.

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

You can check your sql mode by executing this:

SELECT @@sql_mode;

如果设置了任何 NO_ZERO_DATE 或 NO_ZERO_IN_DATE，那么您可以:

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

SET sql_mode = '';

第二个选项

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

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

严格模式影响服务器是否允许 '0000-00-00' 作为有效date:如果未启用严格模式，则允许使用0000-00-00"并且插入不会产生警告.如果启用严格模式，则0000-00-00"为不允许并且插入会产生错误，除非 IGNORE 给出为好.对于 INSERT IGNORE 和 UPDATE IGNORE，允许使用0000-00-00"并且插入会产生警告

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

同样适用于日期时间.

因此，您必须禁用 STRICT MODE 或，如果禁用它不是一个选项 - 修改查询，使其不会返回无效的日期/日期时间结果

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

这篇关于不正确的日期时间值 '0000-00-00 00:00:00' - Date_Sub() 在有的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！