无法获得180天的sql日期。

Question

SELECT CTT.BAN, `Company`, `CID`, `FName`, `MInit`, 
   `LName`, `OName`, `Address`, `City`, `State`, 
   `PostalCode`, `ActiveDate`, `ClosedDate`, `Draft`, 
   `Credit`, `BillingCycle`, `BillingFreq`, `Suspended`, 
   `Paperless` , BTT.Bal 
FROM CustomerT CTT
  JOIN BalanceT BTT
ON (CTT.BAN = BTT.BAN)
WHERE  `Paperless` !=  '1'
   AND `BankDraft` != -1
   AND `CreditCard` != -1
   AND (`BillingCycle` = '1' OR `BillingCycle` = '0')
   AND `Bal` > 2 
   AND (`AccountClosedDate` IS NULL OR 
            DATE(`AccountClosedDate`) >= (NOW() - INTERVAL 180 DAY)  )

所有的工作与这个查询，但180日期peice，我已经尝试了几件事情从这个网站，没有运气。我只需要把过去的6个月都包括在桌子上。

Answer 1

用DATE试试这个

SELECT CTT.BAN, `Company`, `CID`, `FName`, `MInit`, `LName`, `OName`, `Address`, `City`, `State`, `PostalCode`, `ActiveDate`, `ClosedDate`, `Draft`, `Credit`, `BillingCycle`, `BillingFreq`, `Suspended`, `Paperless` , BTT.Bal 
FROM CustomerT CTT
JOIN BalanceT BTT ON
(CTT.BAN = BTT.BAN)
WHERE  `Paperless` !=  '1'
AND `BankDraft` != -1
AND `CreditCard` != -1
AND (`BillingCycle` = '1' OR `BillingCycle` = '0')
AND `Bal` > 2 
AND (`AccountClosedDate` IS NULL OR DATE(`AccountClosedDate`) >=DATE_ADD(CURDATE(), INTERVAL -180 DAY))

或者使用( CURDATE() - INTERVAL 180 DAY )

SELECT CTT.BAN, `Company`, `CID`, `FName`, `MInit`, `LName`, `OName`, `Address`, `City`, `State`, `PostalCode`, `ActiveDate`, `ClosedDate`, `Draft`, `Credit`, `BillingCycle`, `BillingFreq`, `Suspended`, `Paperless` , BTT.Bal 
FROM CustomerT CTT
JOIN BalanceT BTT ON
(CTT.BAN = BTT.BAN)
WHERE  `Paperless` !=  '1'
AND `BankDraft` != -1
AND `CreditCard` != -1
AND (`BillingCycle` = '1' OR `BillingCycle` = '0')
AND `Bal` > 2 
AND (`AccountClosedDate` IS NULL OR DATE(`AccountClosedDate`) >=( CURDATE() - INTERVAL 180 DAY ))