title: Incorrect DateTime Value '0000-00-00 00:00:00' - Date_Sub() in Having
body: <p>So I have an insert which looks like this:</p>
  
  <pre><code>  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) &lt; SUM(NetAmount) THEN 0 ELSE -1 END AS Netted
  
        FROM invoice
        WHERE NetAmountAbs &gt;= 500 and InvoiceDate IS NOT null
        GROUP BY FK_SupplierID,
                 NetAmountAbs
        HAVING COUNT(*) &gt; 1
        AND ((SUM(CASE WHEN NetAmount &gt;= 0 THEN 1 ELSE 0 END)) &gt; 1)
        AND (MAX(SuppInvNoNums) != MIN(SuppInvNoNums))
        AND (MIN(InvoiceDate) != MAX(InvoiceDate))
        AND (DATE_SUB(MAX(COALESCE(InvoiceDate, NOW())) &lt;= 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;
  </code></pre>
  
  <p>It takes from a table, performs some calculations, joins on itself and then inserts. It failed with this message: <code>Incorrect DateTime Value '0000-00-00 00:00:00'</code>. I have narrowed it down and if I remove this line from the having clause <code>(DATE_ADD(MAX(COALESCE(InvoiceDate, NOW())) &lt;= MIN(COALESCE(InvoiceDate, NOW())), INTERVAL -30 DAY))</code> it works.</p>
  
  <p>In fact, it works when I remove the insert strangely.</p>
  
  <p>The select doesn't return anything yet it still fails.</p>
  
  <p>Why is this? Can anyone help find out why?</p>