sql - T-SQL case 语句中的 datediff 在运行时引发溢出错误
问题描述
下面的SELECT
语句来自一个存储过程,它可以在没有两个日期比较列的情况下完美运行,AFIOTD
并且CustomerOTD
. (这些旨在返回1
if [startdate] <= [enddate]
, else 0
。)当我启用列时,我的调用代码会引发溢出错误。
调用来自MS Access 2016
VBA 模块,并且此 SP 正在运行MS SQL 2016
。
存储过程填充 DAO 记录集。
SELECT COL.COMMODITY_CODE, Rec.INVOICE_ID, Rec.INVOICE_DATE, Rec.CUSTOMER_ID, Cust.NAME, Cust.ADDR_1, Cust.CITY, Cust.STATE, COL.PART_ID, Bustout.PART_NUMBER,
Bustout.AUX, Rec_Line.AMOUNT, ShipLine.SHIPPED_QTY, Shipper.PACKLIST_ID, COL.PROMISE_DATE, COL.DESIRED_SHIP_DATE,
CASE
WHEN DATEDIFF(day, CAST(Rec.INVOICE_ID AS datetime), CAST(COL.PROMISE_DATE AS datetime)) >= 0
THEN CAST(1 AS int)
ELSE CAST(0 AS int)
END AS AFIOTD,
CASE
WHEN DATEDIFF(day, CAST(Rec.INVOICE_ID AS datetime), CAST(COL.DESIRED_SHIP_DATE AS datetime)) >= 0
THEN CAST(1 AS int)
ELSE CAST(0 AS int)
END AS CustomerOTD
FROM dbo.AFI_PARTS_BUSTOUT AS Bustout RIGHT OUTER JOIN
AFIRALSRV06.AFI.dbo.SHIPPER_LINE AS ShipLine RIGHT OUTER JOIN
AFIRALSRV06.AFI.dbo.SHIPPER AS Shipper ON ShipLine.PACKLIST_ID = Shipper.PACKLIST_ID RIGHT OUTER JOIN
AFIRALSRV06.AFI.dbo.RECEIVABLE_LINE AS Rec_Line ON Shipper.INVOICE_ID = Rec_Line.INVOICE_ID AND Shipper.PACKLIST_ID = Rec_Line.PACKLIST_ID LEFT OUTER JOIN
AFIRALSRV06.AFI.dbo.CUST_ORDER_LINE AS COL ON Rec_Line.CUST_ORDER_ID = COL.CUST_ORDER_ID AND Rec_Line.CUST_ORDER_LINE_NO = COL.LINE_NO ON
Bustout.PART_NUMBER = COL.PART_ID RIGHT OUTER JOIN
AFIRALSRV06.AFI.dbo.RECEIVABLE AS Rec INNER JOIN
AFIRALSRV06.AFI.dbo.CUSTOMER AS Cust ON Rec.CUSTOMER_ID = Cust.ID ON Rec_Line.INVOICE_ID = Rec.INVOICE_ID
WHERE (Rec.INVOICE_DATE >= CONVERT(DATETIME, @InvoiceDateStart, 102) AND Rec.INVOICE_DATE <= CONVERT(DATETIME, @InvoiceDateEnd, 102))
AND (NOT (Shipper.PACKLIST_ID IS NULL))
解决方案
推荐阅读
- powerbi - DAX 建议在 PowerBI 中生成特定加权平均度量?
- python - Python 请求我收到连接重置错误
- python - pytest - 在所有其他测试之后重新运行失败的测试
- global-variables - Angular 6 - 一些组件使用相同的随机数据,只调用一次 - 如何避免“未定义”
- java - 如何使用 SwingWorker 从无 gui 类更新 Java Jframe 控件
- haskell - 难以理解 Haskell 中的类型
- function - 为什么 TypeScript 编译器在我的示例中忽略了“noImplicitThis”选项?
- json - 由于 enum-groovy 将 json 转换为对象模型的问题
- powershell - 在网络驱动器上搜索 .msg 文件以查找关键字
- python - 根据 Pandas 中另一列中的索引从列中获取数据