SQL Server 2005: Round returns incorrect value if I use a float variable

rounding, sql, sql-server

Solution

I wouldn't recommend using the `float` datatype for exact `decimal` values.

In this particular case, you could convert your `@foo` variable to a `decimal`:

SET @bar = ROUND(CAST(@foo as DECIMAL(10,2)), 1) --> 5.6

What Every Computer Scientist Should Know About Floating-Point Arithmetic

Problem

In SQL Server 2005, I am getting incorrect values when I try to round a value stored in a float variable. In the example below, I expect that both calls to the ROUND function should return 5.6: ``` DECLARE @foo float; DECLARE @bar float; DECLARE @baz float; SET @foo = 5.55; SET @bar = ROUND(@foo, 1) --> 5.5 SET @baz = ROUND(5.55, 1) --> 5.6 ``` What am I doing wrong?

Original source