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

I'm facing this strange issue in SQL Server when dividing a float that it's stored in variable vs when just use the value.

The case is the following:

DECLARE @FLOAT FLOAT = 6.80
print ROUND(@FLOAT / 2, 2, 0);  
print ROUND(@FLOAT / 2, 2, 1);

It prints:

3.4
3.39

The second ROUND that truncates the value is giving an wrong value. It should be 3.4 and not 3.39.

But when I use the value without the variable it works properly:

print ROUND(6.80 / 2, 2, 0);  
print ROUND(6.80 / 2, 2, 1);

It prints:

3.400000
3.400000

Can anybody help me understanding this issue? Thanks in advance.

CodePudding user response：

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