Why does db2 timestampdiff return error SYSFUN:07?

Question

Given a query like this one:

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

Using IBM DB2 z/OS 12 with IDAA, you may get this error:

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

Answer 1

In some cases the char cast may return a leading space, so the timestampdiff argument will be something like ' 00000000000000.000000'. This argument will return the SYSFUN:07 error in certain circumstances.

The fix is to cast to char(22):

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