CODEWITHSUNDEEP
sqlsql-serverrounding
sparks
sparks

Reputation: 1294

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

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?

Upvotes: 5

Views: 3563

Answers (1)

sgeddes
sgeddes

Reputation: 62831

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

Upvotes: 6

Related Questions