I have a problem I've been dealing with that seemed simple at the outset but has proven to be more difficult then I thought. I have this bit of arithmatic I'm doing in SQL server:

select @sum =
floor(

convert(numeric(38,10), @value_1) / convert(numeric(38,10), value_2) * convert(numeric(38,10), @round_power) + 0.5 )
/ convert(numeric(38,10), @round_power

)

I'm using the numeric conversions becuase I need specific decimal precision (i.e. - 1.23499999999 in the place of 1.235 won't cut it ). This was working fine for a while, until I encountered a new problem with decimal numbers being dropped. If I use these values:

@value_1 = 3650.49300
@value_2 = 10000.0
@round_power = 100,000,000.0 (commas for readability)

when you plug these in and run it you end up with 0.365049 not 0.3650493. The problem occures right off the bat with the first division operation. I could switch to floats but then I'd be reintroducing the first problem. Also, I've already tried squaring all of the variables in the equation and taking the square root afterwards. This actually worked for both the rounding and truncating problems and made me feel smart. Naturally, I wasn't allowed to introduce it into production (for some reason...). Talking to a collegue brought up the idea that the squaring introduced some kind of implicit float conversion, but why would this not cause the imprecision I see when I just use floats? I don't know...anyone? oh yeah, any solution has to work in SYBASE also (12.5)...sorry...