Menu
I'm seeing some strange behavior when rounding in SQL Server 2008. Given the following code:
DECLARE @Value REAL
SELECT @Value = .35
SELECT ROUND(@Value, 1)
I would expect the value to be .4, however it outputs .3. I must assume this is because the value stored is actually less than .35, something like .34999999999999. Is this the case, or am I doing something wrong? Is there a way to ensure this behaves as expected, at least from the visible value?
395
The REAL data type is an approximate number with floating point numeric data. REAL value is approximate which means not all range of values can be represented exactly. REAL is equivalent to FLOAT(24).
SQL Server ROUND() Function The ROUND() function rounds a number to a specified number of decimal places.
float is used to store approximate values, not exact values. It has a precision from 1 to 53 digits. real is similar but is an IEEE standard floating point value, equivalent to float(24). Neither should be used for storing monetary values.
When you are using floating-point values like REAL and FLOAT (same thing), the SQL Server ROUND() function follows IEEE Standard 754 and uses the "round up" algorithm.
But that means different things for different floating-point values. Some ".5" values end up getting stored as an approximation of ".49999999999", others as ".500000001", etc. It rounds up the value that is actually stored, not the value you gave it to begin with.
http://msdn.microsoft.com/en-us/library/ms187912.aspx
If exact decimal math matters to you, use DECIMAL, not FLOAT or REAL.
152
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
