如何在日期和时间之间运行查询？ [英] How to run a query between dates and times?

问题描述

我有一个查询，我需要从两个不同的日期和时间提取信息。

我想把昨天和今天之间最后修改的日期以及上次修改的时间为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）作为零售，
 A1.CHDLM， 
 A1.CHTLM 
从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和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 
  

解决方案

也许这样：

  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'
）
  

I have a query where I need to pull information from two different dates and times.
I want to pull everything that was date last modified between yesterday and today and between the time last modified of 18:00:00 hours from yesterday and 13:00:00 hours of today.

How can I accomplish this?

  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

解决方案

Maybe this:

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

---

For more generality (to catch the case when the two dates are not consecutive), it should be:

 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'
     )

这篇关于如何在日期和时间之间运行查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！