SQL Server Rounding Error, Giving Different Values

Rounding issue with SQL Server and REAL datatype

When you are using floating-point values like REAL and FLOAT (same thing), the SQL Server ROUND() function follows IEEE Standard 754 and uses the "round up" algorithm.

But that means different things for different floating-point values. Some ".5" values end up getting stored as an approximation of ".49999999999", others as ".500000001", etc. It rounds up the value that is actually stored, not the value you gave it to begin with.

http://msdn.microsoft.com/en-us/library/ms187912.aspx

If exact decimal math matters to you, use DECIMAL, not FLOAT or REAL.

SQL Server appears to round incorrectly

As has been mentioned in the comments, the "problem" is the data type, not the expression.

If we take the below example:

SELECT [ID],
       SUM(FloatWeighting*FloatMark) AS Float,
       SUM(Decimalweighting*DecimalMark) AS Decimal,
       CONVERT(decimal(18,12),SUM(FloatWeighting*FloatMark)) AS ConvertedFloat,
       ROUND(SUM(FloatWeighting*FloatMark),4) AS RoundedFloat,
       ROUND(SUM(Decimalweighting*DecimalMark),4) AS RoundedDecimal
FROM (VALUES(73652,'Flight Dynamics and Control Exam      ',CONVERT(float,0.75),CONVERT(decimal(3,2),0.75),CONVERT(float,0.905),CONVERT(decimal(4,3),0.905)),
            (73652,'Flight Dynamics and Control Coursework',CONVERT(float,0.25),CONVERT(decimal(3,2),0.25),CONVERT(float,0.92),CONVERT(decimal(4,3),0.92)))V(ID,Exam,FloatWeighting,DecimalWeighting,FloatMark,DecimalMark)
GROUP BY ID;

If you run this, you get the following results:

ID    Float   Decimal ConvertedFloat RoundedFloat RoundedDecimal
----- ------- ------- -------------- ------------ --------------
73652 0.90875 0.90875 0.908750000000 0.9088       0.90880

Notice that the rounded decimal value provides the value you expect, but the rounded float does not. This is because, as discussed in the comments, the floating point value isn't a base 10 value, but a base 2 value. As a result the float value 0.90875 isn't actually 0.90875, it's 0.908749999999999946709294817992486059665679931640625. Notice that this value is actually (just) less than 0.90875.

Because of this when you apply the function ROUND the value above is used, and when you ROUND that value to 4 decimal places then 0.9087 is the correct answer.

The real solution here, therefore, is to fix your design and ALTER your table to make the columns a decimal. As an example, that might be:

ALTER TABLE dbo.tblCourseComponents ALTER COLUMN Mark decimal(4,3);

Note that you would need to use an appropriate scale and precision for your actual values.

If you can't change the data type, or at least not right now, then you can explicitly convert the value to a decimal first before your arithmetic. For example:

ROUND(SUM(CONVERT(decimal(3,2),Weighting)*CONVERT(decimal(4,3),Mark)),4)

Again, ensure you use appropriate precisions and scales for your data .

SQL Server - Strange round behaviour when using variable vs just value

This behaviour is expected. In the first expression you are using a float, and in the latter literal decimal. These two data types are very different. The former is a Base 2 value, and the latter Base 10.

6.80 cannot be stored precisely using Base 2, and so it's actual value is closer to the value 6.79999999999999982236431605997495353221893310546875. As you can see, this is ever so slightly less than 6.8 and so when you divide that number by 2, you get a number ever so slightly less than 3.40,probably 3.399999999999999911182158029987476766109466552734375.

As a result ROUND is rounding the number correctly, as the above value rounded to 2 digits is 3.39 not 3.40.

For the literal, 6.80 is represented exactly, and so is 6.80 / 2 (3.40) so when you round that number again to 2 decimal places, you still get 3.40

SQL Server Rounding Issue

ROUND() is working as it was intended to. You specified to round to 2 decimal places, and that's what you got.

Returns a numeric value, rounded to the specified length or precision.

Rounding means that a digit of 5 or above goes up to nearest, less than 5 down to nearest.

so,

PRINT ROUND(1890.125000, 2) 

produces 1890.130000

Whereas

PRINT ROUND(1890.124999, 2) 

produces 1890.120000

T-SQL Rounding Issue

If you requirement is that all of the results have to add up to the original value (common for calculating taxes), you just can't round each value.

You need to look into rounding of previous calculation and round up or down depending on how previous rounding worked.

For calculation with 2 percentages following formula will work:

declare @amount2 smallmoney = 6.16
declare @percent1 decimal(5,2) = 56.25
declare @percent2 decimal(5,2) = 43.75

select cast((@amount2 * @percent1)/100  AS NUMERIC(36,2))
--select cast((@amount2 * @percent2)/100  AS NUMERIC(36,2))
select cast(@amount2 - cast((@amount2 * @percent1)/100  AS NUMERIC(36,2)) AS NUMERIC(36,2))

select cast((@amount2 * @percent1)/100  AS NUMERIC(36,2)) + cast(@amount2 - cast((@amount2 * @percent1)/100  AS NUMERIC(36,2)) AS NUMERIC(36,2)) -- The total here 6.17. I need 6.16

SQL Server Rounding Issue where there is 5

If you do want to use banker's rounding in SQL Server...

CREATE FUNCTION BankersRounding(@value decimal(36,11), @significantDigits INT)        
RETURNS MONEY        
AS        
BEGIN        
    -- if value = 12.345 and signficantDigits = 2...        

    -- base = 1000        
    declare @base int = power(10, @significantDigits + 1)        

    -- roundingValue = 12345        
    declare @roundingValue decimal(36,11) = floor(abs(@value) * @base)        
    -- roundingDigit = 5        
    declare @roundingDigit int = @roundingValue % 10        

    -- significantValue = 1234        
    declare @significantValue decimal(36,11) = floor(@roundingValue / 10)        
    -- lastSignificantDigit = 4        
    declare @lastSignificantDigit int = @significantValue % 10        

    -- awayFromZero = 12.35        
    declare @awayFromZero money = (@significantValue + 1) / (@base / 10)        
    -- towardsZero = 12.34        
    declare @towardsZero money = @significantValue / (@base / 10)        

    -- negative values handled slightly different        
    if @value < 0        
    begin        
        -- awayFromZero = -12.35        
        set @awayFromZero = ((-1 * @significantValue) - 1) / (@base / 10)        
        -- towardsZero = -12.34        
        set @towardsZero = (-1 * @significantValue) / (@base / 10)        
    end        

    -- default to towards zero (i.e. assume thousandths digit is 0-4)        
    declare @rv money = @towardsZero        
    if @roundingDigit > 5        
        set @rv = @awayFromZero  -- 5-9 goes away from 0        
    else if @roundingDigit = 5         
    begin        
        -- 5 goes to nearest even number (towards zero if even, away from zero if odd)        
        set @rv = case when @lastSignificantDigit % 2 = 0 then @towardsZero else @awayFromZero end        
    end        

    return @rv        

end        

SQL Rounding to 2 decimals places Issue

This can happen if your decimal-like values are in fact floats. Here is a repro:

DECLARE @test TABLE (line INT, amount FLOAT);
INSERT INTO @test VALUES
(1, 6.525),
(2, 6.524999999999999);

SELECT line, amount, FORMAT(amount, 'G17') AS dotnet_g17_formatted, ROUND(amount, 2) AS amount2
FROM @test

Result:

| line | amount | dotnet_g17_formatted | amount2 |
|------|--------|----------------------|---------|
| 1    | 6.525  | 6.5250000000000004   | 6.53    |
| 2    | 6.525  | 6.5249999999999986   | 6.52    |

You can see that float values are stored as an approximation and displayed as such.

The most appropriate solution is to store financial values as DECIMAL.

SQL Server - Rounding off issue

Please, try with below query from float to numeric SQL-FIDDLE:

DECLARE @MinimumTime numeric(32,15),
@filter VARCHAR(MAX)
SET @MinimumTime = 43885.664166666664241

SELECT @filter =  COALESCE('[Time.Minimum] >= ' + cast(@MinimumTime as varchar(MAX)), '')
select @filter

Note:

The basic difference between Decimal/Numeric and Float :
Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly.
Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type reane can be represented exactly with precision and scale.

---

Related Topics