Re: SQL timestampdiff problem

[Alan Campin <alan0307d@xxxxxxxxx>]

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 <
dfreinkel@xxxxxxxxxxxxxxxxx> 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. If precompiling, the error occurred when converting
> a
> numeric constant to the same attributes as column HOURS00001. A list of the
> error types follows: -- Error type 1 is overflow. -- Error type 2 is
> floating point overflow. -- Error type 3 is floating point underflow. --
> Error type 4 is a floating point conversion error. -- Error type 5 is not
> an
> exact result. -- Error type 6 is numeric data that is not valid. -- Error
> type 7 is DBCS data that is not valid. 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.
>
>
>
>
>
> TIA
>
>
>
> Darryl Freinkel | Assignment 400 Group, Inc.
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.