SUBSTRING(TAXFY1 , LEN(TAXFY1), 1) when TaxFy1 = numeric (12,2) cannot be possible - you can only substring strings, not numeric data types.Arthur My Blog

I am having problem with converting varchar to numeric which have a large value the error is arithmetic overflow error converting expression to data type int My EXAM_Temp Table here you will see a value below acct taxfy1 0001 12282981000 acct = varchar(10) taxfy = varchar(11) my regular table Exam acct = varchar(10) TaxFy1 = numeric(12,2) My purpose is to convert taxfy to numeric to insert to the other table SELECT ACCT , TaxFy, SUBSTRING(TAXFY1, LEN(TAXFY1), 1) AS Expr1,SubString(TAXFY1, 1, 10) AS Expr2,NULL AS TAXFY1_NEW INTO #Temp FROM EXAM_Temp UPDATE #TEMP SET TAXFY1_NEW = CASE a.Expr1 WHEN '0' THEN Convert(numeric, (a.Expr2 + '0')) WHEN '{' THEN Convert(numeric, (a.Expr2 + '0')) WHEN 'A' THEN Convert(numeric, (a.Expr2 + '1')) WHEN 'B' THEN Convert(numeric, (a.Expr2 + '2')) WHEN 'C' THEN Convert(numeric, (a.Expr2 + '3')) WHEN 'D' THEN Convert(numeric, (a.Expr2 + '4')) WHEN 'E' THEN Convert(numeric, (a.Expr2 + '5')) WHEN 'F' THEN Convert(numeric, (a.Expr2 + '6')) WHEN 'G' THEN Convert(numeric, (a.Expr2 + '7')) WHEN 'H' THEN Convert(numeric, (a.Expr2 + '8')) WHEN 'I' THEN Convert(numeric, (a.Expr2 + '9')) WHEN '}' THEN Convert(numeric, (a.Expr2 + '0')) * -1 WHEN 'J' THEN Convert(numeric, (a.Expr2 + '1')) * -1 WHEN 'K' THEN Convert(numeric, (a.Expr2 + '2')) * -1 WHEN 'L' THEN Convert(numeric, (a.Expr2 + '3')) * -1 WHEN 'M' THEN Convert(numeric, (a.Expr2 + '4')) * -1 WHEN 'N' THEN Convert(numeric, (a.Expr2 + '5')) * -1 WHEN 'O' THEN Convert(numeric, (a.Expr2 + '6')) * -1 WHEN 'P' THEN Convert(numeric, (a.Expr2 + '7')) * -1 WHEN 'Q' THEN Convert(numeric, (a.Expr2 + '8')) * -1 WHEN 'R' THEN Convert(numeric, (a.Expr2 + '9')) * -1 ELSE Convert(numeric,(a.Expr2 + a.Expr1)) END FROM #Temp a, EXAM_Temp b WHERE a.ACCT = b.ACCT INSERT INTO EXAM(ACCT, TAXFY1) SELECT ACCT, (TAXFY1_NEW * .01) FROM #Temp ORDER BY ACCT what am i going wrong here? thanks.

If I understand the code correctly, it looks like you are trying to insert the value 12282981000 into a NUMERIC(12,2) which is not possible. The precision of a numeric is the total number of digits you can have in the value. In your case, you are specifically stating a scale of 2 which prevents you from having more than 10 digits to the left of the decimal during conversion. Hopefully that helps.