Conversion failed when converting the nvarchar to int
10,642
Solution 1
You also need to perform the checks and conversion in the WHERE clause:
SELECT
id,
CASE WHEN isnumeric([other08]) = 1 THEN CAST([other08] AS INT) ELSE 0 END
FROM CER
WHERE sourcecode LIKE 'ANE%'
AND CASE WHEN isnumeric([other08]) = 1 THEN CAST([other08] AS INT) ELSE 0 END > 720
Solution 2
You need to use IsNumeric in your where clause, to avoid trying to compare strings to the number 720. Eg:
select id, case(isnumeric([other08])) when 1 then [other08] else 0 end
from CER
where sourcecode like 'ANE%' and ISNUMERIC(other08) = 1 and other08 > 720
EDIT
As @Abs pointed out, the above approach won't work. We can use a CTE to compute a reliable field to filter on, however:
WITH Data AS (
select id
, case WHEN isnumeric([other08]) THEN CAST([other08] AS int) else 0 end AS FilteredOther08
, CER.*
from CER
where sourcecode like 'ANE%'
)
SELECT *
FROM Data
WHERE [FilteredOther08] > 720
Related videos on Youtube
Author by
Abs
Updated on June 04, 2022Comments
-
Abs about 2 years
I have a field which is varchar and contains numbers and dates as strings. I want to update all numbers in this field that is greater than 720. I have attempted firstly to do a select but I get this error:
Conversion failed when converting the nvarchar value '16:00' to data type int.
This is my query:
select id, case(isnumeric([other08])) when 1 then [other08] else 0 end from CER where sourcecode like 'ANE%' --and other08 > 720
It fails when I uncomment the last part.
I am trying to get all numerics greater than 720, but I can't do the comaprison. It also fails when casting and converting.
Thanks all for any help
-
Winston Smith over 13 yearsThis relies on short circuiting, and the SQL engine evaluating the WHERE conditions in the order specified. I'm not sure if that's the case - perhaps someone can clarify.
-
Abs over 13 yearsI think you need a
= 1
- but this wouldn't work either as the where condition will still test whether all fields (other08) is greater than 720 and fail. There is no ordering in the where clause. I tried this and it did not work for me. -
dsolimano over 13 years@Abs, you're right about the =1, that's what I get for retyping and not copying, edited to reflect. You're also correct about the short circuiting issue, but I think a CTE should fix that problem
-
Abs over 13 yearsThe query isn't correct syntactically but your logic is flawless. It worked for me great!
-
Abs over 13 years+1 Yes, that would work as well dsolimano - relatively complex though.
-
Winston Smith over 13 years@Abs - the perils of hastily typed answers. Updated with correct syntax.