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

Question

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?

Answer 1

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