Do you use the FLOAT datatype? If so you had better read this……
Today I came across an issue somebody posted on the Microsoft Connect site citing a bug with how the FLOAT data type was calculating a value when being cast to various other datatypes. You can read the original connect item here – CAST money AS FLOAT then INT returns unexpected result for 0.57 when FLOAT is multiplied by 100
PRINT CAST(CAST(CAST(0.57 as money) AS FLOAT)*100 AS INT)
I would expect the result to be 0.57 but the result returned is 56. Also returns unexpected values for 0.29 and 0.58.
The reason I am blogging about this is that Microsoft state the following in their documentation:
“Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. “
The above quote has been taken from this source – float and real (Transact-SQL)
Because of this I suggested converting to a decimal datatype (as this resolves the FLOAT rounding issue) instead of a float which does not exhibit the same behaviour. I would suggest that if you use float for anything matchematical that you review your code pretty sharpish!
The original contributor did not show how he found the issue, but I have run the following test which does confirm his results and thought I would share them with you.
The first step is to create a table to hold our results. The first attribute holds the initial value we wish to convert. The second attribute holds the value being converted via the FLOAT datatype and the third attribute is the value after being converted with the DECIMAl datatype. Here’s the code:
CREATE TABLE #Results
(InitialValue SQL_Variant
,FloatMethod INT
,DecimalMethod INT);
GO
There’s a choice at this point to go down the RBAR route or create a set based solution. RBAR means a lot more code and well it’s rubbish so let’s go set based as it’s pretty straight forward in this case.
The first thing to do is to populate the table with a list of numbers we wish to convert. In this case I am building up a list of numbers using some system tables rather than builiding a while loop and inserting that way:
INSERT INTO #Results (InitialValue)
SELECT TOP (999)
ROW_NUMBER() OVER(ORDER BY sc.object_id) * 0.001 AS [InitialValue]
FROM SYS.COLUMNS SC
CROSS APPLY SYS.TABLES ST
GO
The next thing to do is to update the values that are not yet populated. We’re going to do this with a single UPDATE statement. I’ve had to use a conditional case statement otherwise things go a little awry.
UPDATE #Results
SET FloatMethod =
CASE WHEN LEFT(CAST(InitialValue AS CHAR(6)),4) = ’0.00′ THEN
CAST(CAST(CAST(InitialValue as money) AS FLOAT)*1000 AS INT)
ELSE CAST(CAST(CAST(InitialValue as money) AS FLOAT)*100 AS INT)
END,
DecimalMethod =
CASE WHEN LEFT(CAST(InitialValue AS CHAR(6)),4) = ’0.00′ THEN
CAST(CAST(CAST(InitialValue as money) AS DECIMAL(9,4))*1000 AS INT)
ELSE CAST(CAST(CAST(InitialValue as money) AS DECIMAL(9,4))*100 AS INT)
END;
GO
Now we have our table populated all we need to do is to return the data where the values for FloatMethod and DecimalMethod differ:
SELECT
InitialValue,
FloatMethod,
DecimalMethod
FROM #Results
WHERE FloatMethod <> DecimalMethod;
GO
DROP TABLE #Results;
GO
You can of course write this as one statement, but the above is easier to conceptualise. If you want it in one statement then here it is:
SELECT
InitialValue,
FloatMethod,
DecimalMethod
FROM
(SELECT
InitialValue,
FloatMethod =
CASE WHEN LEFT(CAST(InitialValue AS CHAR(6)),4) = ’0.00′ THEN
CAST(CAST(CAST(InitialValue as money) AS FLOAT)*1000 AS INT)
ELSE CAST(CAST(CAST(InitialValue as money) AS FLOAT)*100 AS INT)
END,
DecimalMethod =
CASE WHEN LEFT(CAST(InitialValue AS CHAR(6)),4) = ’0.00′ THEN
CAST(CAST(CAST(InitialValue as money) AS DECIMAL(9,4))*1000 AS INT)
ELSE CAST(CAST(CAST(InitialValue as money) AS DECIMAL(9,4))*100 AS INT)
END
FROM
(SELECT TOP (999)
ROW_NUMBER() OVER(ORDER BY sc.object_id) * 0.001 AS [InitialValue]
FROM SYS.COLUMNS SC
CROSS APPLY SYS.TABLES ST) A) B
WHERE B.FloatMethod <> B.DecimalMethod;
I’d love to hear if you use the FLOAT datatype for mathemetical computations and if this post has helped.
Rich
