My simple code (attached) is getting error "Arithmetic overflow error converting varchar to data type numeric."

0
votes

I'm getting this frustrating error:

Arithmetic overflow error converting varchar to data type numeric

Note: hours is Varchar(50) & BIGCLEAN is numeric(18, 2)

With the following code: 

UPDATE [dbo].[KSFTABLE] SET [BIGCLEAN] = case 
    when [hours] is NULL then NULL 
    when [hours] = ' ' then NULL 
    when [hours] = '-' then NULL 
    else cast ([hours] as numeric(18,2)) 
end ;
1
sql
In the code above there is a ` before an else. - Mike Nakis
It would help if you told us which sql product you are using, what its version is, whether you are receiving this error as soon as your sql product tries to parse this statement or whether you are receiving it as it is processing data, and if the second is true, then on exactly what value for hours you are receiving this error. - Mike Nakis
Mike- I wrote back but took too long, sorry. Here it is. 1- MSFT SQL Server Management Studio 17. 2- It occurs right away. Here is the exact comment that I receive: "Msg 8115, Level 16, State 6, Line 27 Arithmetic overflow error converting varchar to data type numeric. The statement has been terminated." 4- that duplicated last line with the apostrophe is NOT part of my code - I didn't realize I typed it and dont know how to remove it now. - Karen PS -- Maybe I should have added this: UPDATE [dbo].[KSFTABLE] that is, my "Sorry. I need to update the code above to show that - Karen Fireman
The part that says 'The statement has been terminated' leads me to believe that your statement is successfully parsed, and you are receiving this error as ms-sql is executing it on actual data, so it is choking on a specific strange value of hours. I think you need to look for suspicious values in your hours column. - Mike Nakis
Also, how is BIGCLEAN defined? - Mike Nakis

1 Answers

0
votes

May be the output cast is even bigger than numeric(18, 2).Try with numeric(18,4) or numeric(18,5) etc.