"Round half up" on floating point values

Question

We are stuck with a database that (unfortunately) uses floats instead of decimal values. This makes rounding a bit difficult. Consider the following example (SQL Server T-SQL):

SELECT ROUND(6.925e0, 2)   --> returns 6.92

ROUND does round half up, but since floating point numbers cannot accurately represent decimal numbers, the "wrong" result (from the point of view of the end-user) is displayed. I understand why this happens.

I already came up with two possible solutions (both returning a float, which is, unfortunately, also a requirement):

1. Convert to a decimal data type before rounding: SELECT CONVERT(float, ROUND(CONVERT(decimal(29,14), 6.925e0), 2))
2. Multiply until the third digit is on the left-hand side of the decimal point (i.e. accurately represented), and then do the rounding: SELECT ROUND(6.925e0 * 1000, -1) / 1000

Which one should I choose? Is there some better solution? (Unfortunately, we cannot change the field types in the database due to some legacy applications accessing the same DB.)

Is there a well-established best practice solution for this (common?) problem?

(Obviously, the common technique "rounding twice" will not help here since 6.925 is already rounded to three decimal places -- as far as this is possible in a float.)

Answer 1

Your first solution seems safer, and also seems like a conceptually closer fit to the problem: convert as soon as possible from float to decimal, do all relevant calculations within the decimal type, and then do a last minute conversion back to float before writing to the DB.

Edit: You'll likely still need to do an extra round (e.g. to 3 decimal places, or whatever's appropriate for your application) immediately after retrieving the float value and converting to decimal, to make sure that you end up with the decimal value that was actually intended. 6.925e0 converted to decimal would again be likely (assuming that the decimal format has > 16 digits of precision) to give something that's very close to, but not exactly equal to, 6.925; an extra round would take care of this.

The second solution doesn't look reliable to me: what if the stored value for 6.925e0 happens to be, due to the usual binary floating-point issues, a tiny amount too small? Then after multiplication by 1000, the result may still be a touch under 6925, so that the rounding step rounds down instead of up. If you know your value always has at most 3 digits after the point, you could fix this by doing an extra round after multiplying by 1000, something like ROUND(ROUND(x * 1000, 0), -1).

(Disclaimer: while I have plenty of experience dealing with float and decimal issues in other contexts, I know next to nothing about SQL.)

Answer 2

Use an arbitrary-precision format such as DECIMAL. That way you can leave it to the language to get it right (or wrong as the case may be).