Roundoff error in sql

Question

DECLARE @TAX VARCHAR(30)=120.45
DECLARE @TaxRoundOf VARCHAR(30)
SET @TaxRoundOf=ROUND(@TAX,1)
SELECT @TaxRoundOf

This Gives Result (@TaxRoundOf=120.5)

DECLARE @TAX VARCHAR(30)=146.45
DECLARE @TaxRoundOf VARCHAR(30)
SET @TaxRoundOf=ROUND(@TAX,1)
SELECT @TaxRoundOf

This Gives Result (@TaxRoundOf=146.4)

But I need to return 146.50 . why this mismatch between two results? any one can help plz?

it's quite evident that the round function is rounding down. You must use the proper function to round up instead. — James, Jun 11 '18 at 08:05
Why are you using `VARCHAR`? If you were to store your numbers using a numeric data type, you would get the results you are expecting. — GarethD, Jun 11 '18 at 08:06
Convert to decimal or numeric and then apply round function. Should work — Mahesh Malpani, Jun 11 '18 at 08:11
Why would you be rounding values with two decimal places (xxx.45) to a value with two decimal places but rounded to only 1 (xxx.50). That seems unusual. — Gordon Linoff, Jun 11 '18 at 11:03

score 2 · Answer 1 · answered Jun 11 '18 at 08:15

Since you are using VARCHAR to store your numbers, SQL Server is having to do implicit conversion to float behind the scenes, which is having knock on effects on your calculations. You can reproduce this using the below query:

SELECT  ROUND(CONVERT(FLOAT, 120.45),1),            -- 120.5
        ROUND(CONVERT(FLOAT, 146.45),1),            -- 146.4
        ROUND(CONVERT(DECIMAL(10, 2), 120.45),1),   -- 120.50
        ROUND(CONVERT(DECIMAL(10, 2), 146.45),1)    -- 146.50

Since floating point numbers are not exact, 146.45 cannot be exactly represented as a float, and ends up being stored as a very slightly smaller number, so when this is passed to the round function, it is rounded down, instead of up.

The solution, as demonstrated by the 3rd and 4th columns in the above query, is to use a more precise data type.

Andrei Suvorkov · Answer 2 · 2018-06-11T08:54:46.850

1

You can use this:

SET @TaxRoundOf=ROUND(10 * CAST(@TAX AS FLOAT)) / 10

instead of:

SET @TaxRoundOf=ROUND(@TAX,1)

DEMO

PS as @GarethD already mentioned I wouldn't use @TAX as VARCHAR type.

edited Jun 11 '18 at 08:54

answered Jun 11 '18 at 08:04

Andrei Suvorkov

5,559
5
22
48

Sir In this case its works but if @tax= 146.44 it return 146.50 but right answer is 146.40 is it sir? – Vignesh D Jun 11 '18 at 08:37
It depends on you which is 'right' answer. I have added in 'DEMO' ROUND and CEIL. Just pick up, what is better for you ;) – Andrei Suvorkov Jun 11 '18 at 08:56

jspcal · Accepted Answer · 2018-06-11T08:26:02.273

0

You can also rely on numeric rounding instead of converting your string to a float, which can lose information.

Cast a string to numeric and then round:

select round(cast('146.45' as numeric(18,2)), 1)
-- 146.50

A decimal constant is already a decimal so there's no need to cast it:

select round(146.45, 1)
-- 146.50

edited Jun 11 '18 at 08:26

answered Jun 11 '18 at 08:12

jspcal

50,847
7
72
76

Roundoff error in sql

3 Answers3