I am experiencing a problem when changing the precision of a numeric column in a SQL SERVER table.
The field is like NUMERIC(5) and I would like to change it to NUMERIC(5,2) , but it is displaying the message below
I used ALTER TABLE tabela ALTER COLUMN coluna NUMERIC(5,2);
This table already has data stored.
Does anyone know a solution? Thank you.
Try so you 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 decimal places and at the time of 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);
This occurs because of the numeric field specification of SQL Server where the first parameter is the TOTAL number of digits and the second is how much of that total is reserved for the decimal places. Therefore, specifying Numerical (5,2) is saying to have at most 5 digits being that of these 5, two will be used for the decimal places. Since your table already has data, a 9999 value will populate the 5-digit limit as it will be 9999.00 (6 digits). Change to a numeric of 7.2 and be happy.