Please start any new threads on our new
site at http://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.

Our new SQL Server Forums are live!
Come on over! We've restricted the ability to create new threads on these forums.

select CONVERT(NUMERIC(11, 5), 1202007.00000) [Value_1]
Error:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

select CONVERT(NUMERIC(11, 5), 1202007.00000) [Value_1]
Error:
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

To further clarify The reason is NUMERIC(11, 5) means a precision value of 11 (total of 11 significant digits) and scale of 5 (5 digits after decimal point) which means it can handle only maximum of 11-5=6 digits before decimal (ie maximum value of 999999). the current integer part of value is 1202007 which is above the limit and hence the Arithmetic overflow error Making it 19 as per earlier suggestion will mean it can have upto 14 digits in integer part with maximum range upto 99999999999999

I picked 19 (rather than 18, or 20 or any other number) because of the way exact numerics are stored in SQL server. Precision of 10-19 takes up 9 bytes. If you need to go beyond 19, you might as well go to 28 because precisions 20-28 take up 13 bytes and so on. http://msdn.microsoft.com/en-us/library/ms187746.aspx

Column information is below.
Type Length Precision scale
------- ------- -------- -------
numeric 9 15 5
select CONVERT(NUMERIC(19, 5), 1202007.00000) [Value_1]
I don't want to increase the length since the destination table length has to change..
Please let me know if i truncate the value of the decimal
1202007.0000)

I don't quite know what the length property for numeric data type means. What did you do to retrieve that information? If the precision and scale are 15 and 5, use those:

Please let me know if it is ok if the destination table and column has same datatype

destcolumn numeric(15,5)

depends on whether the column just receives the values as is from the source or does some aggregation on it. If former, you can keep it same as source itself If latter , better to keep it with a higher precision value as result of aggregation may go beyond the source values precision (for ex. source may have values only upto 10 digits but adding all of them might give a result with 11 or 12 digits. So to handle these ,safe bet is to keep precision as 18 so that you'll have field as

It is failing the during the insertion from source to destination for that value.

Please let me know how do i insert the value 1202007.0000 instead 1202007.00000

Thanks for your help in advance..

Nope this value will not cause any issues while inserting to yourtable as it has only 7 digits in its integer part and hence will fit comfortably into decimal field of precision 15 and scale 5. Try the sample code posted by Lamprey and you can see that for yourself.

Please see the below query i have defined datatype numeric & length in the table for the columnA and ColumnB.
I do not want to make any changes to table column datatype and lenght.
DECLARE @SourcetableA TABLE (ColumnA numeric(11, 5))
INSERT @SourcetableA (ColumnA)
VALUES (1202007.00000)
DECLARE @DestinationTableB TABLE (ColumnB numeric(11, 5))
INSERT
@DestinationTableB (ColumnB)
SELECT
ColumnA
FROM
@SourcetableA
SELECT *
FROM @DestinationTableB
Error:
Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

Please see the below query i have defined datatype numeric & length in the table for the columnA and ColumnB.
I do not want to make any changes to table column datatype and lenght.
DECLARE @SourcetableA TABLE (ColumnA numeric(11, 5))
INSERT @SourcetableA (ColumnA)
VALUES (1202007.00000)
DECLARE @DestinationTableB TABLE (ColumnB numeric(11, 5))
INSERT
@DestinationTableB (ColumnB)
SELECT
ColumnA
FROM
@SourcetableA
SELECT *
FROM @DestinationTableB
Error:
Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

this is not we suggested. Here you're using precision as 11 so it will only support maximum of 6 digits before decimal point (ie max value of 999999). Your passed value has integer part as 1202007 which is > 999999 and hence the overflow make it decimal(15,5) and see the difference

Thanks Visakh
We have lots of data and big application and changing the length and data type to decimal might have other implicastions on the other queries.Since it has millions of records in the table.
Can i get the select query which will drop the last trailing 0 in the decimal part if the max size exceeds i.e NUMERIC(11, 5)
that way i do not need to change the table structure.
In the below case
columnA
------------
1202007.00000
Expected output
ColumnA
-------------
1202007.0000
Thanks for your help in advance..

Thanks Visakh
We have lots of data and big application and changing the length and data type to decimal might have other implicastions on the other queries.Since it has millions of records in the table.
Can i get the select query which will drop the last trailing 0 in the decimal part if the max size exceeds i.e NUMERIC(11, 5)
that way i do not need to change the table structure.
In the below case
columnA
------------
1202007.00000
Expected output
ColumnA
-------------
1202007.0000
Thanks for your help in advance..

Just CAST it to decimal with required precision and scale value