December 23, 2010 at 9:37 pm
Hi
Below one is model table, am getting strange issue, i dont know
how overcome this issue
Declare @t table
(
a numeric(4,2)
)
Declare @b-2 varchar(4)
Set @b-2='2004'
Insert into @t
Select ISNULL(CONVERT(decimal(4,2),@b), 0)
In above things, working in trigger. Passing parameter is varchar type. we tried to insert varchar type to number type field. at the time am getting
error message.
Arithmetic overflow error converting varchar to data type numeric.
Even i tried to convert the varchar to numberic, and also am getting same issue.
Can any one please guide, how to convert the above one.
December 23, 2010 at 10:20 pm
The problem is with your scale and precision of NUMERIC and DECIMAL..
You have given "2004" and NUMERIC (4,2)
DECIMAL (scale, precision)
Scale = maximum number of digits that numeric value can have , including the ones before and after the decimal point
Precision = maximum number of digits after the decimal point
Thus , NUMERIC(4,2) implies that you can have maximum of 4 digits, with a mandatory 2 digits after your decimal point. So Query Engine will convert 2004 to 2004.00 [which is NUMERIC(6,2) ] and will try to insert into NUMERIC(4,2) thereby producing the "Arithmetic Overflow" error..
To mitigate this, u can use NUMERIC(6,2) in both your CREATE TABLE and SELECT statements, like
Declare @t table
(
a numeric(6,2)
)
Declare @b-2 varchar(4)
Set @b-2='2004'
Insert into @t
Select ISNULL(CONVERT(decimal(6,2),@b), 0)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply