Conversion error while converting nvarchar to int

Question

I have a field that is varchar and contains numbers and dates as strings. I want to update all numbers in this field that is greater than 720. At first I tried to make a choice, but I get this error:

Conversion failed when converting the nvarchar value '16:00' to data type int.

This is my request:

select id, case(isnumeric([other08])) when 1 then [other08] else 0 end
from CER where sourcecode like 'ANE%' --and other08 > 720

It does not work when I uncomment the last part.

I am trying to get all numbers greater than 720, but I cannot do comaprison. It also fails when casting and converting.

Thank you all for your help.

Answer 1

You also need to perform validation 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

Answer 2

IsNumeric where, 720. :

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

@Abs, . CTE , :

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