Re: SQL timestampdiff problem

If the TIMESTAMP columns are accurately called /date/ fields, is the following expression sufficiently accurate [with excessive growth spanning each leap year] to implement a possible circumvention?:

Update qtemp.arfcred10
set Hours_on_Hold =
dec( ( days(Release_date) - days(hold_date) ) * 24 , 10, 2 )

Regards, Chuck

On 15 May 2013 15:13, Darryl Freinkel wrote:

I have a problem using this SQL command.

In the following example, the SELECT statement works, but when I try
to use it on a UPDATE statement it fails :

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

select release_date, hold_date
, cast( timestampdiff( 8
, cast(Release_date - hold_date as char(22) )
)as dec(10, 2)
)
from qtemp.ARFCRED10
where release_date > '0001-01-01 00:00:00.000000';

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.