admin

如何在日期和时间之间运行查询?

sql

我有一个查询,需要从两个不同的日期和时间提取信息。
我想拉出昨天和今天之间以及从昨天起18:00:00小时到今天13:00:00小时之间的最后修改日期的所有内容。

我怎样才能做到这一点?

  SELECT A1.CHCASN, 
         A1.CHTRKN,
         SUM(A2.CDPAKU) AS UNITS, 
         A1.CHACWT, 
         SUM(A2.CDPRC * A2.CDPAKU) AS COST, 
         SUM(A3.STRPRC * A2.CDPAKU) AS RETAIL, 
         A1.CHDLM, 
         A1.CHTLM
    FROM CHCART00 A1, 
         CDCART00 A2, 
         STSTYL00 A3
   WHERE A1.CHCASN = A2.CDCASN
     AND A2.CDSTYL = A3.STSTYL
     AND A2.CDCOLR = A3.STCOLR
     AND A2.CDSDIM = A3.STSDIM
     AND A1.CHSTAT = '25'
     AND A1.CHROUT = 'UPSCA'
     AND A1.CHDLM BETWEEN 20110505 And 20110506
     AND A1.CHTLM >= '160000'
     AND A1.CHTLM <= '130000'
GROUP BY A1.CHCASN, A1.CHTRKN, A1.CHACWT, A1.CHDLM, A1.CHTLM
ORDER BY A1.CHCASN

阅读 217

收藏
2021-07-01

共1个答案

admin

也许是这样的:

 AND ( A1.CHDLM = 20110505 
       AND A1.CHTLM >= '160000' 
     OR
       A1.CHDLM = 20110506
       AND A1.CHTLM <= '130000'
     )

为了更笼统(以捕捉两个日期不连续的情况),应为:

 AND ( A1.CHDLM = 20110505 
       AND A1.CHTLM >= '160000' 
     OR
       A1.CHDLM BETWEEN 20110505 +1
                    AND 20110506 -1
     OR
       A1.CHDLM = 20110506
       AND A1.CHTLM <= '130000'
     )
2021-07-01