CODEWITHSUNDEEP
sqlsql-serverrounding
karthiK
karthiK

Reputation: 17

Rounding off incorrectly in SQL

So when I give the 1st digit after the decimal point as 4 or 5 or 8, I get an incorrectly rounded value as output (shown in 'CASE 1' below). But when I use other numbers (1,2,3,5,6,9,0) in the same place I get the expected result (shown in 'CASE 2' below). Can anyone explain why this is happening?

--CASE 1
DECLARE @factor float = 0.425,
        @amount money = 1
SELECT CONVERT(money, ROUND(@factor * @amount, 2))
--output is 0.42 (should be 0.43)

--CASE 2
SET @factor = 0.625  
SELECT CONVERT(money, ROUND(@factor * @amount, 2))
--output is 0.63 (correct)

Please note, this got fixed by using another variable to store the product - '@factor * @amount' and then use that variable to get the rounding off value. But I am just curious why would I see the discrepancy before. Thanks in advance.

Upvotes: 0

Views: 679

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

Well, the values are just at the brink. 0.425 and 0.625 would get rounded up, while for instance 0.42499999999999 and 0.62499999999999 would get rounded down.

You are using FLOAT, which is not a precise data type, but an approximate one. You can hence only expect approximate results.

If you want exact results, use an exact type like DECIMAL.

Docs: https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15

Upvotes: 4

Related Questions