Subject Re: [IBO] Float to Numeric
Author Helen Borrie
At 10:07 PM 7/01/2004 -0600, you wrote:
>Sorry about the double post but after 4 days of no response in the other
>newsgroup I decided to come here since I'm going to do this with IBObjects
>I have a field that is float. I'm trying to change it to numeric(5,2) but
>have a problem. I create a new field of type numeric(5,2). Then I do a
>update classsubject
>set weight = cast(weight1 as numeric(5,2))
>where weight is not null
>The result is Field Weight is always rounded up. I must keep the 2 decimal
>Any suggestions?

Try this:

update classsubject
set weight = cast (cast (weight1 * 100 as numeric(7,2)) / 100 as numeric(5,2))

If that still seems to give you wrong outcomes, try again, upping the scale
by 1 of the intermediate number by 1. If it's still not what you want,
you'll need to look at using a UDF to do custom rounding algorithm.

Sidenote: Actually, I'd prefer to define the new column with a higher
precision and, if you have to limit it to a maximum, effect that with a
CHECK constraint. It makes no difference whatsoever to the storage size,
since Fb stores a (9,2), (8,2), (7,2), (6,2) or a (5,2) all as 32-bit
integers anyway. To my instinct, a CHECK constraint is the tightest way to
control limits.