T&B <list.sql-s/[EMAIL PROTECTED]> wrote:
I shouldn't have complicated my question by using the word "round". It's not the rounding that I'm trying to do. The input values are already rounded to two decimal places (cents of the dollar).
Realize that floating point numbers are stored by computers in binary, not in decimal. 141.7 cannot be exactly represented in binary (just like 1/3 cannot be exactly represented in decimal). So instead, a nearest representable number is stored, and this number happens to be slightly less than 141.7 - say 141.69999999998. Hence your rounding problems.
For this reason, it is unwise to manipulate monetary values as floating point numbers. It is much better to keep them as integers expressed in pennies, or even finer-grained (many financial calculations are required to be accurate to 0.0001 of a dollar, so it is common to scale by a factor of 10000).
I am trying to output fixed decimal places. So 141.7 should output as 141.70, and 23 should output as 23.00 etc.
Do it in your application, after retrieving the raw values from SQL database. SQL is ill-suited for string formatting.
I think I actually found a fix to the apparent bug in cast
There is no bug in CAST, just your misunderstanding of how it works.
select cast( round(141.70 * 100, 0) as integer) gives the correct: 14170 Now the question is, does this fix the problem, or only for some numbers?
This would work for all but very large numbers - those approaching or greater than 10^13 or so. For such large numbers, there are not enough bits in the double's mantissa to keep absolute error below 0.01.
Igor Tandetnik
----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
