January 15, 2008 at 12:13 am
Hi guys,
I am trying to increase the size of a numeric field already existing in my table1. Currently it is of type numeric(16, 2).
I executed the command:
ALTER TABLE table1
ALTER COLUMN field1 numeric(16,5) NULL
but I got the error:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
Anybody please can provide any solution? I am only increasing the size of the field not decreasing (ie truncating) it.
Thanks!
January 15, 2008 at 12:29 am
Is there any data in the table?
If there is any data check for there is any non-numeric value existing the table
January 15, 2008 at 12:31 am
You are only increasing the number of places after the decimal point, not the size of the field. The length remains at 16 which actually reduces the number before the decimal point from 14 to 11. If you look in your table you will have some numbers over 99,999,999,999.
January 15, 2008 at 12:31 am
So you are shrinking the value range !?!
So it can nolonger store existing content !
Currently it is of type numeric(16, 2).
I executed the command:
ALTER TABLE table1
ALTER COLUMN field1 numeric(16,5) NULL
Max would be ............. 99999999999999.99
Your new max would be 99999999999.99999
How about.
create table #xyz
(col1 int identity(1,1) primary key,
col2 decimal(16,2) null
)
go
insert into #xyz (col2) values (99999999999999.99)
-- (1 row(s) affected)
go
alter table #xyz
alter column col2 decimal(16,5) null
go
-- Server: Msg 8115, Level 16, State 8, Line 1
-- Arithmetic overflow error converting numeric to data type numeric.
-- The statement has been terminated.
alter table #xyz
alter column col2 decimal(19,5) null
go
--(1 row(s) affected)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 15, 2008 at 1:08 am
Thanks a lot for your replies!
I decided to use the numeric(19, 5) instead of numeric(16, 5)..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply