Convert varchar to Decimal Arithmetic overflow error converting varchar to data type numeric

Question

Please can some one help me with this simple sql query

SELECT avg(CAST(NULLIF(Sim1SS,0.0) as DECIMAL)) as MTN ,
avg(CAST(NULLIF(Sim2SS,0.0) as DECIMAL)) as Vodacom

FROM [Networks].[dbo].[Device] 

I am getting the following error:

Arithmetic overflow error converting varchar to data type numeric.

and here is an example of the SS column:

28,99
10,99
11,99
NULL
NULL
31,99
31,99
NULL

Answer 1

You have two problems. First of all when you using NULLIF on a string datatype you are implicitly converting it to a numeric datatype. You have specified 0.0 as a second parameter. SQL Server does not know what to choose and it perhaps selects a float or something else. NULLIF should be called after you convert the string to number

Secondly, you have not specified the precision and scale for your DECIMAL data type. According to MSDN the default precision and scale of the DECIMAL data type is 18,0. If you have not specified the scale the numbers will be truncated.

So, the correct syntax should be:

SELECT avg(NULLIF(CAST(Sim1SS as DECIMAL(18,2)), 0.00)) as MTN,
       avg(NULLIF(CAST(Sim2SS as DECIMAL(18,2)), 0.00)) as Vodacom
FROM [Networks].[dbo].[Device] 

BTW, if your regional settings for decimal symbol is not a period (.) then you need to do a replace:

SELECT avg(NULLIF(CAST(REPLACE(Sim1SS, ',', '.') as DECIMAL(18,2)), 0.00)) as MTN,
       avg(NULLIF(CAST(REPLACE(Sim2SS, ',', '.') as DECIMAL(18,2)), 0.00)) as Vodacom
FROM [Networks].[dbo].[Device] 

If your data is not sanitized you need to use ISNUMERIC function, like this:

SELECT avg(NULLIF(case when isnumeric(Sim1SS)=1 then CAST(REPLACE(Sim1SS, ',', '.') as DECIMAL(18,2)) end, 0.00)) as MTN,
       avg(NULLIF(case when isnumeric(Sim2SS)=1 then CAST(REPLACE(Sim2SS, ',', '.') as DECIMAL(18,2)) end, 0.00)) as Vodacom
FROM [Networks].[dbo].[Device]