MYSQL - 在 WHERE BETWEEN 中使用 CURDATE() 返回 NULL

Question

尝试使用 CURDATE() 过滤过去 30 天的数据，但它只是返回 NULL 行。

查询:

SELECT

vcrs.vcrDate AS DATE,
vcrs.jobNumber AS RO_NUM,
vhcrows.description AS DESC,
vhcrows.partNumber AS PART_NUM,
vhcrows.unitPrice AS UNIT_PRICE,
vhcrows.partQuantity AS QUANTITY,

FROM vhcrows 
INNER JOIN vcrs ON vhcrows.vcr_id = vcrs.uid
INNER JOIN usergroup_manufacturers ON usergroup_manufacturers.usergroup_id = usergroups.uid

WHERE usergroup_manufacturers.manufacturer_id = 7
AND vcrs.vcrDate BETWEEN DATE_FORMAT((CURDATE()),'%Y%m%d') AND DATE_FORMAT((CURDATE() - INTERVAL 30 DAY),'%Y%m%d')
AND vcrs.vcrStatus <> '-1'
AND vcrs.vcrSubStatus = 4
AND vhcrows.rowType IN (1,2);

它应该返回类似这样的内容:

DATE        RO_NUM   DESC                   UNIT_PRICE  QUANTITY
20140805    36815    BALL JOINT             24.85       1
20140821    11763    BALL JOINT             31.4        1
20140806    12076    BOLT                   4.44        2
20140806    12076    BOLT                   4.44        2
20140828    37994    DEF WIPER              9.53        3
20140804    11536    DRIVE MEMBER           106.42      1
20140804    11536    DRIVESHAFT             280.78      1
20140805    36815    NUT                    1.33        1
20140813    56817    RADIO WITH CD PLAYER   1399.88     1
20140820    203102   REF.                   16.7        1
20140801    11517    TENSIONER              64.01       1
20140814    12203    WHEEL BEARING          162.13      1
20140807    51520    WIPER                  9.53        3
20140821    198081   "RANGE"                22.77       1

但是，正如我所说，我只是得到 NULL。

谢谢!

* vcr.vcrDate 存储为例如20140911*

Answer 1

为什么要将 CURDATE() 转换为字符串？只要这样做:

vcrs.vcrDate BETWEEN CURDATE()) AND CURDATE() - INTERVAL 30 DAY

这假设 vcrDate 以 native 日期格式存储。如果没有，请编辑您的问题并解释其存储方式。

如果vcrs.vcrDate实际上是一个整数，那么您希望您的CURDATE()是一个整数。您可以采用相同的路径，但最终转换为整数:

vcrs.vcrDate BETWEEN cast(DATE_FORMAT((CURDATE()),'%Y%m%d') as unsigned) AND
                     cast(DATE_FORMAT((CURDATE() - INTERVAL 30 DAY),'%Y%m%d') as unsigned)