Re: SQL timestampdiff problem

Because every possible value for the results from the expression, with the first argument "8" asking for the "hours", is [approximately] BETWEEN -87600000 AND 87600000, every possible result value fits into a DEC(10,2) result. Thus no possible value, whether cast to a DEC(15, 2) or cast to a DEC(10, 2), could legitimately cause an overflow condition.

The details from the CPF5035 [and CPF5029 or others] that precede the SQL0406 in the joblog where the request was performed, could reveal something more... if only a relative record number on which the error was diagnosed. Nothing I can think of would make the actual error seem sensible; only if the preceding errors revealed something /other/ than what the SQL error alludes.

Regards, Chuck

On 15 May 2013 15:18, Alan Campin wrote:

Is it because you are defining the cast field as 15,2 instead of
10,2?

On Wed, May 15, 2013 at 4:13 PM, Darryl Freinkel wrote:

<<SNIP>>

Update qtemp.arfcred10
set Hours_on_Hold = cast( timestampdiff( 8, cast(Release_date -
hold_date as char(22) ) ) as dec(15, 2) ) ;

<<SNIP>>

Hours_on_Hold is defined as decimal(10,2)

The error reported is:
SQL State: 22003
Vendor Code: -406
Message: [SQL0406] Conversion error on assignment to column HOURS00001.
Cause . . . . . : During an attempt to assign a value to column
HOURS00001 with an INSERT, UPDATE, ALTER TABLE, or REFRESH TABLE
statement, conversion error type 1 occurred. ... A list of the
error types follows: -- Error type 1 is overflow. ...
Recovery . . . : Change the statement so that the result value fits
in column HOURS00001 and is valid, or create the table or view
again, specifying a new type or length for column HOURS00001 so
that the result value can be assigned.
Technical description . . . . . . . . : During precompile, an
attempt is made to convert numeric constants in INSERT and UPDATE
statements to the attributes of the target column. This would
improve performance when the program is called.