sql – Arithmetic overflow error converting numeric to data type numeric

sql – Arithmetic overflow error converting numeric to data type numeric

My guess is that youre trying to squeeze a number greater than 99999.99 into your decimal fields. Changing it to (8,3) isnt going to do anything if its greater than 99999.999 – you need to increase the number of digits before the decimal. You can do this by increasing the precision (which is the total number of digits before and after the decimal). You can leave the scale the same unless you need to alter how many decimal places to store. Try decimal(9,2) or decimal(10,2) or whatever.

You can test this by commenting out the insert #temp and see what numbers the select statement is giving you and see if they are bigger than your column can handle.

I feel I need to clarify one very important thing, for others (like my co-worker) who came across this thread and got the wrong information.

The answer given (Try decimal(9,2) or decimal(10,2) or whatever.) is correct, but the reason (increase the number of digits before the decimal) is wrong.

decimal(p,s) and numeric(p,s) both specify a Precision and a Scale. The precision is not the number of digits to the left of the decimal, but instead is the total precision of the number.

For example:
decimal(2,1) covers 0.0 to 9.9, because the precision is 2 digits (00 to 99) and the scale is 1.
decimal(4,1) covers 000.0 to 999.9
decimal(4,2) covers 00.00 to 99.99
decimal(4,3) covers 0.000 to 9.999

sql – Arithmetic overflow error converting numeric to data type numeric

If you want to reduce the size to decimal(7,2) from decimal(9,2) you will have to account for the existing data with values greater to fit into decimal(7,2). Either you will have to delete those numbers are truncate it down to fit into your new size. If there was no data for the field you are trying to update it will do it automatically without issues