SQL加入条件时间格式错误

Question

我正在使用使用VBA访问"绿屏" AS400 IBM ISERIES的SQL代码，并且我正在通过ADODB连接连接（请让我知道您是否需要有关该连接的更多信息或SQL版本，我是SQL的初学者。

现在，我的代码运行正常，但是我想在最后一个左键中添加另一个"one_answers"条件，以便代码仅考虑存在的数据[（po.stkdt- po.actdt）＆lt;40]。换句话说，（股票日期）减去（创建日期）少于40天。

所以我想添加类似的内容：

AND ( date(timestamp((PO.STKDT + 19000000) concat '000000')) - date(timestamp((PO.ACTDT + 19000000) concat '000000')) ) <40 

代码在没有此条件的情况下运行正常，但是当我添加此条件时，它会给我一个错误" SQL0181：日期，时间或时间戳字符串无效"。

任何帮助都将不胜感激以解决和状况错误

请注意，我在STKDT和ACTDT日期列附加了"时间戳"one_answers"日期"，以将其从1171119到11/19/2017的格式转换。而且我有一个使用此格式正确提取列数据的参数。

感谢您的帮助！

这是我的完整代码：

SELECT X.ITNBR AS ITEM, X.ITDSC AS DESCRIPTION,
X.CRUS AS CREATE_USER, X.INVFG AS INV_FLAG ,
C1.PURPR AS PO_PRICE, C2.STDUC AS STD_COST, 
A.HOUSE AS WHS, A.WHSLC AS WHSLOCATION ,
A.VNDNR AS VENDR,VN.VNAME AS VENDOR_NAME ,
VN.VNAMA, A.MOHTQ AS QTY_ON_HAND,
A.MPUPQ AS QTY_ON_PURCHASE ,A.MALQT AS QTY_ALLOCATED,
(A.MOHTQ+A.MPUPQ-A.MALQT) AS QTY_AVAILABLE,
( (A.MOHTQ+A.MPUPQ-A.MALQT) * C2.STDUC ) AS INV_COST,
K.KIORDCNT AS KB_CARDS, K.KICQTY AS KB_QTY,
K.KICTYP AS KB_TYPE, COUNT( PO.ITNBR ) AS DATA_COUNT ,
AVG(PO.STKPR) AS AVG_PO_PRICE, 
CAST(AVG( date(timestamp((PO.STKDT + 19000000) concat '000000')) - date(timestamp((PO.ACTDT + 19000000) concat '000000')) ) AS DECIMAL(50,2) ) AS AVG_LEADTIME,
SUM(PO.QTYOR) AS ALL_QTY_ORDERED,MIN(PO.QTYOR) AS MIN_QTY_ORDERED,
CAST(AVG(PO.QTYOR) AS DECIMAL(50,2) ) AS AVG_QTY_ORDERED, 
MAX(PO.QTYOR) AS MAX_QTY_ORDERED

FROM SIM.ENT X 
                LEFT JOIN SIM.ITEM A ON (A.ITNBR=X.ITNBR) 
                LEFT JOIN SIM.ITMR C1 ON (C1.ITNBR=A.ITNBR) 
                LEFT JOIN SIM.ITMV C2 ON (C2.ITNBR=A.ITNBR) 
                LEFT JOIN SIM.VENN VN ON (VN.VNDNR=A.VNDNR) 
                LEFT JOIN SIM.KBIM K  ON (K.KIITEM=A.ITNBR AND K.KIHOUSE=A.HOUSE)
                LEFT JOIN SIM.POIT PO ON (PO.ITNBR=A.ITNBR AND PO.HOUSE=A.HOUSE AND PO.VNDNR=A.VNDNR)
                AND PO.STAIC !=99 
                AND PO.ACTDT between '" & Range("AA1") & "' and  '" & Range("AB1") & "' ***AND MYTIMECONDITIONHERE***
            WHERE X.ITNBR != 'TEST%'

GROUP BY X.ITNBR, X.ITDSC,X.CRUS,X.INVFG, C1.PURPR, C2.STDUC 
        ,A.HOUSE, A.VNDNR,VN.VNAME, VN.VNAMA, A.WHSLC
        ,A.MOHTQ,A.MPUPQ,A.MALQT,K.KIORDCNT, K.KICQTY, K.KICTYP`

Answer 1

当您要查看的只是日期时，为什么要转换为时间戳？

要将数字cyymmdd转换为实际日期，您需要构建一个包含" yyyy-mm-dd"的字符串...因此，您需要这样的东西：

select date(                                                
            substring(char(dec(1171120,7) + 19000000),1,4)  
            concat '-' concat                               
            substring(char(dec(1171120,7) + 19000000),5,2)  
            concat '-' concat                               
            substring(char(dec(1171120,7) + 19000000),7,2)  
           ) as theDate                                     
from sysibm.sysdummy1                                       

而不是在语句中转换它，而是构建一个将其转换的UDF。或更好的是，下载这样的UDF，我建议Idate

Answer 2

首先，我会将您的po的过滤条件从加入子句中移出（它们不是加入标准），然后将其移至Where子句中。这只是语义上的事情，根本不会影响查询。它只是使事情变得更容易理解，因为所有过滤器条件都位于一个地方，并且所有联接标准都在另一个位置。然后，我将使用timestamp_format将数字日期更改为timestamp值。然后将timestamp的s转换为自1月1日以来的日子。

SELECT X.ITNBR AS ITEM, 
       X.ITDSC AS DESCRIPTION,
       X.CRUS AS CREATE_USER, 
       X.INVFG AS INV_FLAG,
       C1.PURPR AS PO_PRICE, 
       C2.STDUC AS STD_COST, 
       A.HOUSE AS WHS, 
       A.WHSLC AS WHSLOCATION,
       A.VNDNR AS VENDR,
       VN.VNAME AS VENDOR_NAME,
       VN.VNAMA, 
       A.MOHTQ AS QTY_ON_HAND,
       A.MPUPQ AS QTY_ON_PURCHASE,
       A.MALQT AS QTY_ALLOCATED,
       (A.MOHTQ+A.MPUPQ-A.MALQT) AS QTY_AVAILABLE,
       ((A.MOHTQ+A.MPUPQ-A.MALQT) * C2.STDUC) AS INV_COST,
       K.KIORDCNT AS KB_CARDS, 
       K.KICQTY AS KB_QTY,
       K.KICTYP AS KB_TYPE, 
       COUNT(PO.ITNBR) AS DATA_COUNT ,
       AVG(PO.STKPR) AS AVG_PO_PRICE, 
       CAST(AVG(date(timestamp((PO.STKDT + 19000000) concat '000000')) -
           date(timestamp((PO.ACTDT + 19000000) concat '000000'))) 
           AS DECIMAL(50,2)) AS AVG_LEADTIME,
       SUM(PO.QTYOR) AS ALL_QTY_ORDERED,
       MIN(PO.QTYOR) AS MIN_QTY_ORDERED,
       CAST(AVG(PO.QTYOR) AS DECIMAL(50,2)) AS AVG_QTY_ORDERED, 
       MAX(PO.QTYOR) AS MAX_QTY_ORDERED
FROM SIM.ENT X 
    LEFT JOIN SIM.ITEM A ON (A.ITNBR=X.ITNBR) 
    LEFT JOIN SIM.ITMR C1 ON (C1.ITNBR=A.ITNBR) 
    LEFT JOIN SIM.ITMV C2 ON (C2.ITNBR=A.ITNBR) 
    LEFT JOIN SIM.VENN VN ON (VN.VNDNR=A.VNDNR) 
    LEFT JOIN SIM.KBIM K  ON (K.KIITEM=A.ITNBR AND K.KIHOUSE=A.HOUSE)
    LEFT JOIN SIM.POIT PO 
        ON (PO.ITNBR=A.ITNBR AND PO.HOUSE=A.HOUSE AND PO.VNDNR=A.VNDNR)
WHERE X.ITNBR != 'TEST%'
    AND PO.STAIC != 99 
    AND PO.ACTDT between '" & Range("AA1") & "' and  '" & Range("AB1") & "' 
    AND days(timestamp_format(PO.STKDT + 19000000, 'YYYYMMDD')) - 
        days(timestamp_format(PO.ACTDT + 19000000, 'YYYYMMDD')) < 40
GROUP BY X.ITNBR, X.ITDSC,X.CRUS,X.INVFG, C1.PURPR, C2.STDUC 
        ,A.HOUSE, A.VNDNR,VN.VNAME, VN.VNAMA, A.WHSLC
        ,A.MOHTQ,A.MPUPQ,A.MALQT,K.KIORDCNT, K.KICQTY, K.KICTYP

Answer 3

您可能需要检查日子功能。如果您的原始字段是有效的日期字段，则您的表达式将很简单：

DAYS(PO.STKDT) - DAYS(PO.ACTDT) < 40

首先通过日期/时间戳以您的方式进行转换稍复杂，但仍应工作。另请参阅：DB2中两个日期之间计算多少天？