Change numerical field accuracy SQL SERVER

Asked

Viewed 9,331 times

2

I am facing a problem when changing the accuracy of a numerical column in an SQL SERVER table.

The countryside is like NUMERIC(5) and would like to change it to NUMERIC(5,2), but is presenting the message below

I used ALTER TABLE tabela ALTER COLUMN coluna NUMERIC(5,2);

  • "Arithmetic overflow error when converting NUMERIC to NUMERIC data type".

This table already has stored data.

Does anyone know any solution ? Thank you.

sql-server

  • post the create table of this table.

  • I edited my answer to make sure it works

  • Show!! It worked! Thank you.

2 answers

4


Try this will change the column type:

ALTER TABLE tabela ALTER COLUMN coluna NUMERIC(5,2);

For your specific problem I believe that the error is not the SQL command but the presence of some value that has 5 whole decimal places and at the time of the error conversion because in the new type NUMERIC(5,2) to 3 decimal places.

change the table to 

ALTER TABLE tabela ALTER COLUMN coluna NUMERIC(7,2);

  • Thanks dude, but I already tried this command and it presents that message. I forgot to put the command I used. Thanks.

2

This is due to the SQL Server numeric field specification, where the first parameter is the TOTAL number of digits and the second is how much of this total is reserved for decimals. Therefore, specifying Numerico(5,2) is saying to have a maximum of 5 digits and of these 5, two will be used for the decimals. As your table already has data, a value 9999 will exceed the limit of 5 digits as it will be 9999.00 (6 digits). Switch to 7.2 numeric and be happy.

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.