Error converting data type varchar to float
Search and search on SO and can't figure it out
Tried CASTING on each field as FLOAT to no avail, converter didn't get me any further
How can I get the below case clause to return the value specified in the THEN section?
Error: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to float.
my SQL query that makes the 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
Hooray!
source to share
Try using ISNUMERIC to handle strings that cannot 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
source to share
The problem you are having is that you are specifically looking for strings that contain a character %
and then converting them (implicitly or explicitly) to float.
But strings containing characters %
cannot be converted to floats while they still have %
. This also results in an error:
select CONVERT(float,'12.5%')
If you want to convert to float, you need to remove the sign first %
, for example:
CONVERT(float,REPLACE(terms_code,'%',''))
will just eliminate it. I'm not sure if there are other characters in the column terms_code
that might turn it off as well.
You also need to be aware that SQL Server can reorder operations quite aggressively and therefore may try to perform the above conversion on other strings in terms_code
, even those that do not contain %
. If this is the source of your error, you need to prevent this aggressive reordering. If there are no aggregates, the expression CASE
can usually avoid the worst problems - make sure any strings you don't want to process are eliminated by earlier clauses WHEN
before trying to convert
source to share