CODEWITHSUNDEEP
sqlsql-serverdatabaserounding
Dexter
Dexter

Reputation: 203

SQL BUG : The ROUND() method of SQL is not working properly - Issue in rounding off for some values

I am using a round method in my SQL proc and passing the rate parameter and ideally (as per the below code I am using) the round method should round off the value to 2 decimal places but it is acting weird.

But for other rate value, it is working fine and rounding off the number properly.

SQL Server version : SQL Server 8

Code Snippet :

ISNULL(round(rate,2),'')

Here, the value of rate is 3.025.

Current output - 3.02

Expected Output - 3.03 (After rounding off the value)

Also, I have observed that for the numbers (1.025 and 2.025), round method is also behaving the same way and not rounding it off as expected value.

Question 1 : Can anyone suggest, why this is happening and is there any existing bug at SQL server side similar to this ? I tried finding any bug but didn't reached close.

Question 2 : Is there any other way where I can replace the round method in the existing code, which can resolve this issue and also work properly for existing rate records ?

Upvotes: 0

Views: 880

Answers (1)

Derrick Moeller
Derrick Moeller

Reputation: 4970

Is RATE a FLOAT? As pointed out by Jeroen 3.025 can't be perfectly represented by FLOAT and is getting rounded down. Perhaps changing the type to DECIMAL will generate the behavior you're looking for.

DECLARE @float FLOAT = 3.025
DECLARE @decimal DECIMAL(4,3) = 3.025
DECLARE @money MONEY = 3.025

PRINT @float                -- 3.025
PRINT @decimal              -- 3.025
PRINT @money                -- 3.03

PRINT ROUND(@float, 2)      -- 3.02
PRINT ROUND(@decimal, 2)    -- 3.030
PRINT ROUND(@money, 2)      -- 3.03

Upvotes: 3

Related Questions