Re: SQL timestampdiff problem

[CRPence <CRPbottle@xxxxxxxxx>]

  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.