How Do I Handle a “Error converting data type” Error?

I’m using Windows 10 and SQL SMS 2014. When I run a query, I get the following error message: 

“Error converting data type varchar to float.”  Can you help in troubleshooting this error message?

Usually when SQL tries to convert a number, such as 1.25 from text to float, it can do so easily; however, it does get hung up and will throw an error converting data if there are some non-numeric characters in the values.

For example, converting 1.25 would work, but converting $1.25 would not, since the server wouldn’t know how to deal with the “$“

My suspicion is that you have some data in the column you’re trying to convert that isn’t “purely” numeric.

If you go through the data, may be you see some alphabetical data there.

Here are some examples you can try in the query editor:

--EXAMPLES
DECLARE @myText as varchar(20)
DECLARE @myFloat as Money

--This Works!  :)
Print 'Try 1.20 * 1.10'
SET @myText = '1.20'
SET @myFloat = @myText * 1.10
Print @myFloat

--This Will throw an error   :(
PRINT 'Try $1.20 * 1.10'
SET @myText = '$1.20'
SET @myFloat = @myText * 1.10
Print @myFloat

Depending on what you’re trying to do with your data, such as using it in a query, you may have to use an IIF function in conjunction with ISNUMERIC to test if the column value can be converted, and if not, then display another suitable value.

The ISNUMERIC function returns 1 if the value tested is numeric; otherwise 0. It can be used to test up-front whether characters can be tested to numeric data types.

Here is an example:

SELECT IIF(ISNUMERIC(myTexttoFloatColumn) = 1,
           myTextToFloatColumn*1.50, 
           myTexttoFloatColumn)
FROM   mySampleTable

This would either multiply the column by 1.5, if the column can be converted to a number, or just display the original value…

Hope this helps!