Tuesday, February 21, 2006

Convert Float Stored As Varchar To Numeric

An interesting problem came up today in the microsoft SQL Server DTS newsgroup
A person tried to convert a varchar value to numeric and got the following message "Error converting data type varchar to numeric."
The problem is that the value stored as varchar is a float and then SQL Server is not able to convert this to a numeric datatype
So first you have to convert to float and then to numeric


DECLARE @Value VARCHAR(50)
SELECT @Value = '5.9499999999999997E-2'

--Error converting data type varchar to numeric.
SELECT CONVERT(NUMERIC(21,20),@Value)

--This works after converting to float first
SELECT CONVERT(NUMERIC(21,20),CONVERT(FLOAT,@Value))


--No problems here since the value in the varchar field can be converted to numeric
DECLARE @Value2 VARCHAR(50)
SELECT @Value2 = '5.9499999999999997'

No comments: