1

I have a field in a SQL Server 2012 database that is currently decimal(30,20) because that is the format of the data provider. However, that large of a decimal is completely unnecessary and would like to trim it down to something like decimal(16,7) considering this table has millions of rows.

I tried the following SQL:

alter table mytable alter column mycolumn decimal(16,7)

But that resulted in the error:

Arithmetic overflow error converting numeric to data type numeric.

What is the best way to change this field? I obviously realize the error is occurring because the values in the column exceed the new precision. What I'm wondering is if there is a script that can cut off any digits past the 7th decimal point and then convert the column data type?

Ricketts
  • 5,025
  • 4
  • 35
  • 48

1 Answers1

0

Reduce to 7 decimals:

update mytable set newcolumn = convert(decimal(16, 7), mycolumn);

update mytable set mycolumn = null;

commit;

alter table mytable alter column mycolumn decimal(16,7)

update mytable set mycolumn = newcolumn;

commit;

alter table mytable drop column newcolumn;
Klas Lindbäck
  • 33,105
  • 5
  • 57
  • 82
  • Tried this solution...It made all values in the format of: ###.#######00000000000000000000. When I re-run the alter column script, still get the same error. – Ricketts Apr 16 '13 at 17:49
  • Try `convert` instead. You may also need to create a temporary column for the converions (see updated answer). – Klas Lindbäck Apr 17 '13 at 07:33