Search code examples
sqlsql-server-2008numericvarchar

Conversion failed when converting the varchar value '71.8' to data type int

I am trying to convert varchar(20) field to numeric(10,2) and for this I am using this script:

SELECT CAST(CASE Age WHEN 'NULL' THEN 0 ELSE Age END AS numeric(10,2)) AS Age 
FROM   AgeTable

But I am getting this error:

Conversion failed when converting the varchar value '71.8' to data type int.

Solution

  • Basically, same as @Mikael Eriksson's solution, only with more syntactic sugar:

    SELECT ISNULL(CAST(NULLIF(Age, 'NULL') AS numeric(10, 2)), 0)
FROM AgeTable