Error rounding a number in SQL Server

Question

I'm trying the following using SQL Server 2008 R2 and SQL Server 2012 and I get the same results in both.

If I write the following statement:

select round(4.005, 2)

I get the expected result: 4.01

However if I write the following statements:

declare @result float
select @result = 4.005
select round(@result, 2)

I get an unexpected result: 4

But if I replace float with real in the previous statements:

declare @result real
select @result = 4.005
select round(@result, 2)

I get the expected result.

Can anyone tell me why is this happening?

Answer 1

yes I had the same issue. I tested out some solutions...

SELECT round(cast(3.175 as float), 2) as roundingBAD,
CAST(round(TRY_CONVERT(DECIMAL(28,2), cast(3.175 as float)), 2) as nvarchar(max)) as roundconvertBAD, cast(CAST(round(CAST(3.175 as DECIMAL(18,10)), 4) as decimal(18,2)) as nvarchar(max)) as roundconvert3, cast(FORMAT(round(CAST(3.175 as DECIMAL(18,10)), 2), '0.######') as nvarchar(max)) as roundconvert4, cast(CAST(CAST(3.175 as DECIMAL(18,10)) as decimal(18,2)) as nvarchar(max)) as roundconvert5P, cast(CAST(CAST(3.175 as DECIMAL(18,10)) as decimal(18,1)) as nvarchar(max)) as roundconvert1DP

output: 3.17 3.17 3.18 3.18 3.18 3.2

I went with the last two options in my production Microsoft SQL Server code for rounding floats correctly.

Answer 2

This has to do with float being an approximate data type.

see: http://technet.microsoft.com/en-us/library/ms187912(v=sql.105).aspx

The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types. Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types. Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

The IEEE 754 specification provides four rounding modes: round to nearest, round up, round down, and round to zero. Microsoft SQL Server uses round up. All are accurate to the guaranteed precision but can result in slightly different floating-point values. Because the binary representation of a floating-point number may use one of many legal rounding schemes, it is impossible to reliably quantify a floating-point value.

Answer 3

because you haven't specify the number of bits that are used to store the mantissa of the float number in scientific notation , try this,

declare @result float(5)
select @result = 4.005
select round(@result, 2)

• SQLFiddle Demo
• float and real (Transact-SQL)