Forum Discussion

Copper Contributor

Jun 02, 2021

Is this a bug of sql server 2017?

Dear All: I found on the server that the conversion failed (SUM(CAST(testkey AS FLOAT)) part) when using the CAST conversion type, but I used another table to replace #tbs, and found that it can ...

olafhelper

Bronze Contributor

Jun 03, 2021

AlanNsw , agree with you.

Even if you add an addition filter like

AND ISNUMERIC(testkey) = 1

there is no garantuee that the query processor don't hit a non-convertable value.

Other example for proof: The first return 1 = true, means the value looks like a numeric one, but the second fails with conversion failure.

SELECT ISNUMERIC('-')
GO
SELECT CONVERT(float, '-')

Olaf

AlanNsw

Copper Contributor

Jun 03, 2021

Dear olafhelper
Thanks for your advice.
I used CASE WHEN ISNUMERIC(testkey) = 1 THEN CAST(testkey AS FLOAT) ELSE 0 END to solve the problem, because it is always equal to true. I don’t understand why it will cause a conversion error, and there is no conversion failure on other servers

Alan