使用 SQL 和 DB2 for i 进行日期算术 [英] Date Arithmetic with SQL and DB2 for i

问题描述

我正在尝试运行将更新特定表中信息的 SQL 查询.我遇到的问题是我需要查询来提取今天的日期，无论查询运行的日期是什么，然后只提取可以追溯到几年的记录.然后，我希望它在这两年的时间段内返回 CUR_CASH_UNITS 的 AVG.我已经尝试过 DATEDIFF 和 DATEADD ，但它们似乎不适用于我拥有的系统，因为它返回 *libl 错误.这是我所拥有的:

I am trying to run a SQL query that will update information in a specific table. the problem I am running into is that I need the query to pull today's date, for whatever the date may be when the query is run, and then only pull records going back to years. I then want it to return an AVG of the CUR_CASH_UNITS over that two year time period. I have tried DATEDIFF and DATEADD and those do not appear to work with the system I have because it returns a *libl error. Here is what I have:

Select  Month,
    SSSALESNAM,
    CAT,
    Sum(Units) as Units     
From    DW****.******EG
Inner Join  (Select CAT,
                STORE,
                MONTH(DATE) As Month,
                Sum(CUR_CASH_UNITS) As Units
         From   DW*******.****AT        
         Inner Join (Select CAT,
                            CATNUM
                     From   DW****.*****ST) As Category_List             
        On         DW*******.****AT.CATEGORY_NUMBER=Category_List.CATNUM
        --Where  (((CUR_CASH_UNITS.MONTH(DATE)) Between MONTH(DATE) And (MONTH(DATE)-24MONTH))
        Group by Cat,
                 Store,
                 MONTH(DATE)) As Cash_Units

On          DW****.******EG.SSSTR=Cash_Units.STORE           
Group By    Month,
        SSSALESNAM,
        CAT
Order By    Month,
        SSSALESNAM

上面被注释掉的部分只是我试图让日期算术工作的尝试之一.使用 Where 语句注释掉正确执行的查询.我表中的日期列标记为 DATE，格式为 YYYY-MM-DD.对 SQL 来说有点新，所以如果我忽略了一些简单的事情，我提前道歉.

The section above that is commented out is just one of my attempts to get the date arithmetic to work. With the Where statement commented out the query executed correctly. The date column in my table is labeled as DATE with a format of YYYY-MM-DD. Kind of new to SQL so I apologize in advance if it is something simple that I am overlooking.

推荐答案

我想你想要一个 where 子句，如下所示:

I think you want a where clause like the following:

where "date" between current date - 2 years and current date

您需要双引号，因为 date 是保留字.剩下的只是为了得到过去两年的约会.

You need the double quotes because date is a reserved word. The rest is just for getting a date in the past two years.

这篇关于使用 SQL 和 DB2 for i 进行日期算术的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！