为什么db2 timestampdiff返回错误SYSFUN:07

Question

给定这样一个查询：

select timestampdiff(4, char(ORDER_DT - ORDER_DT)) as TEST
from mytable;

使用带有IDAA的IBMDB2z/OS 12，您可能会得到以下错误：

ROUTINE SYSFUN.TIMESTAMPDIFF (SPECIFIC NAME TIMESTAMPDIFF) 
HAS RETURNED AN ERROR SQLSTATE WITH DIAGNOSTIC TEXT SYSFUN:07.
SQLCODE=-443, SQLSTATE=38552.

Answer 1

在某些情况下，char强制转换可能返回前导空格，因此时间戳diff参数将类似于' 00000000000000.000000'。在某些情况下，此参数将返回SYSFUN:07错误。

修复方法是强制转换为char(22):

select timestampdiff(4, cast(ORDER_DT - ORDER_DT as char(22))) as TEST
from mytable;