Error converting data type varchar to float

sql, sql-server, t-sql

Solution

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

Problem

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!

Original source