Re: SQL timestampdiff problem

[CRPence <CRPbottle@xxxxxxxxx>]

  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.