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.
>Solution :
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