Error converting data type varchar to float

3
votes

Searched and searched on SO and can't figure it out
Tried CASTING each field as FLOAT to no avail, convert didn't get me any further
How can I get the below case clause to return the value stated in the THEN section?

Error: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to float.

section of my SQL query that makes it error:

When cust_trendd_w_costsv.terms_code like '%[%]%' and (prod.dbo.BTYS2012.average_days_pay) - (substring(cust_trendd_w_costsv.terms_code,3,2)) <= 5 THEN prod.dbo.cust_trendd_w_costsv.terms_code

average_days_pay = float
terms_code = char

Cheers!

3
sqlsql-servertsql
Please post a sample of the data for the two fields used in your query. - Glenn Stevens
The thing is in substring(cust_trendd_w_costsv.terms_code,3,2) expression contain improper value for convert - Aleksandr Fedorenko

3 Answers

9
votes

Try to use ISNUMERIC to handle strings which can't be converted:

When cust_trendd_w_costsv.terms_code like '%[%]%' 
and (prod.dbo.BTYS2012.average_days_pay) - 

(case when isnumeric(substring(cust_trendd_w_costsv.terms_code,3,2))=1 
         then cast(substring(cust_trendd_w_costsv.terms_code,3,2) as float)
         else 0
 end)
<= 5 THEN prod.dbo.cust_trendd_w_costsv.terms_code
4
votes

The issue that you're having is that you're specifically searching for strings that contain a % character, and then converting them (implicitly or explicitly) to float.

But strings containing % signs can't be converted to float whilst they still have a % in them. This also produces an error:

select CONVERT(float,'12.5%')

If you're wanting to convert to float, you'll need to remove the % sign first, something like:

CONVERT(float,REPLACE(terms_code,'%',''))

will just eliminate it. I'm not sure if there are any other characters in your terms_code column that may also trip it up.

You also need to be aware that SQL Server can quite aggressively re-order operations and so may attempt the above conversion on other strings in terms_code, even those not containing %. If that's the source of your error, then you need to prevent this aggressive re-ordering. Provided there are no aggregates involved, a CASE expression can usually avoid the worst of the issues - make sure that all strings that you don't want to deal with are eliminated by earlier WHEN clauses before you attempt your conversion

1
votes

If your are sure that Substring Part returns a numeric value, You can Cast The substring(....) to Float :

 .....and (prod.dbo.BTYS2012.average_days_pay) - (CAST(substring(cust_trendd_w_costsv.terms_code,3,2)) as float ) <= 5 ....