What cause the error 'Arithmetic overflow error converting varchar to data type numeric'?

Question

I use the query below to convert a column 'Characteristic__c' of varchar(255) into DECIMAL(22,8). The value of Characteristic__c was specified as simply 0.0, but it still cause error 'Arithmetic overflow error converting varchar to data type numeric.'. Don't know the reason and thanks for help.

SELECT CONVERT(DECIMAL(22,8) , Characteristic__c) FROM  [ODS].[dbo].
[Scorecard_Details__c]
WHERE Attribute__c='Employment Duration' and Characteristic__c=0.0 and 
WOE__c=0

varchar(255) must be too wide to fit into decimal(22, 8). Just because a given value is 0.0 does not mean that the space it takes up reflects that amount. — Tim Biegeleisen
The issue is just with the WHERE clause, I believe. You'll get the same error with something like: select 1 from (values('1')) t(a) where t.a = 0.0;. The fix probably depends on what your data looks like. — ZLK

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-10-11T01:20:48

Presumably, Characteristic_c cannot be converted to a numeric. In SQL Server 2012+, you can simply use TRY_CONVERT():

SELECT TRY_CONVERT(DECIMAL(22, 8) , Characteristic__c)
FROM [ODS].[dbo].[Scorecard_Details__c]
WHERE Attribute__c = 'Employment Duration' AND
      TRY_CONVERT(DECIMAL(22, 8), Characteristic__c) = 0.0 AND 
      WOE__c = 0;

Note: If WOE__c is a character column, you should use TRY_CONVERT() there too.

What cause the error 'Arithmetic overflow error converting varchar to data type numeric'?

2 Answers