DB2/ODBC上SQL中的日期算法

Question

我正在构建一个针对DB2数据库的查询，通过IBM Client Access ODBC驱动程序进行连接。我想根据字段"a.ofbkddt"拉出小于6天的字段。问题是这个字段不是一个日期字段，而是一个十进制字段，格式为YYYYMMDD。

我能够通过将其包装在char()调用中来分解十进制字段，然后使用substr()来提取年、月和日字段。然后我将其格式化为日期，并调用days()函数，该函数给出一个可以对其进行算术运算的数字。

下面是一个查询示例:

select
       days( current date) -
       days( substr(char(a.ofbkddt),1,4) concat '-'              -- YYYY-
             concat substr(char(a.ofbkddt),5,2) concat '-'       -- MM-
             concat substr(char(a.ofbkddt),7,2) ) as difference, -- DD
       a.ofbkddt as mydate
from QS36F.ASDF a

结果如下:

difference  mydate
2402        20050402
2025        20060306
...
4           20110917
3           20110918
2           20110919
1           20110920

这是我期望看到的…但是，当我在查询的where子句中使用相同的逻辑时:

select
       days( current date) -
       days( substr(char(a.ofbkddt),1,4) concat '-'              -- YYYY-
             concat substr(char(a.ofbkddt),5,2) concat '-'       -- MM-
             concat substr(char(a.ofbkddt),7,2) ) as difference, -- DD
       a.ofbkddt as mydate
from QS36F.ASDF a
where
(
days( current date) -
days( substr(char(a.ofbkddt),1,4) concat '-'         -- YYYY-
      concat substr(char(a.ofbkddt),5,2) concat '-'  -- MM
      concat substr(char(a.ofbkddt),7,2) )           -- DD
) < 6

我没有从我的查询中得到任何结果，尽管很明显我am得到的日期差异只有1天(显然小于我在where子句中请求的6天)。

我的第一个想法是days()的返回类型可能不是整数，导致比较失败…根据在http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/rbafzmst02.htm上找到的days()的文档，它返回一个bigint。为了安全起见，我将差值转换为整数，但这没有效果。

Answer 1

你是在反着做这件事。与其对表中的每个值都使用函数(以便与日期进行比较)，不如预先计算日期中的差异。在每一行上运行这个函数会消耗你的资源——如果你能对CURRENT_DATE这样做，你会节省很多资源(如果你能在你的应用程序代码中这样做，可能会节省更多的资源，但我意识到这可能是不可能的)。毕竟，日期是以可排序的格式显示的。

查询如下:

SELECT ofbkddt as myDate
FROM QS36F.ASDF
WHERE myDate > ((int(substr(char(current_date - 6 days, ISO), 1, 4)) * 10000) +
                (int(substr(char(current_date - 6 days, ISO), 6, 2)) * 100) +
                (int(substr(char(current_date - 6 days, ISO), 9, 2))))

在对样本数据表运行时，会产生以下结果:

myDate
=============
20110917
20110918
20110919
20110920

您可能还想创建一个日历表，并将这些日期添加为其中一列。

Answer 2

如果您尝试一个公共表表达式呢?

WITH A AS
(
    select
       days( current date) -
       days( substr(char(a.ofbkddt),1,4) concat '-'              -- YYYY-
             concat substr(char(a.ofbkddt),5,2) concat '-'       -- MM-
             concat substr(char(a.ofbkddt),7,2) ) as difference, -- DD
       a.ofbkddt as mydate
    from QS36F.ASDF a
)
SELECT
    *
FROM
    a
WHERE
    difference < 6

Answer 3

您的数据在a.ofbkddt中有一些空值吗?这可能会导致db2在计算小于操作时出现一些奇怪的行为。