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

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query

Author  Topic 

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-06-24 : 16:22:06
[code]
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.
[/code]

Please help me to resolve the error.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-24 : 17:13:59
quote:
Originally posted by sqlfresher2k7


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.


Please help me to resolve the error.

Change to a larger precision
select CONVERT(NUMERIC(19, 5), 1202007.00000) [Value_1]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-25 : 00:48:49
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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-25 : 09:19:18
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
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-07-01 : 11:51:19
Thanks for the response.


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)






Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-01 : 12:58:21
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:
select CONVERT(NUMERIC(15, 5), 1202007.00000) [Value_1]
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-01 : 13:34:02
SELECT CAST(1202007.0000 AS DECIMAL(15,5))

EDIT: That's what I get for hitting reply and then going off to a meeting before clicking submit.. :)
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-07-01 : 15:27:10
Thanks Lamprey

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


destcolumn numeric(15,5)

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 01:57:42
quote:
Originally posted by sqlfresher2k7

Thanks Lamprey

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

decimal(18,5)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-07-02 : 09:49:36
Thanks

I got an error after making the changes

CAST([Value_1] AS DECIMAL(15,5))

"Conversion failed because the data value overflowed the specified type"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-02 : 09:51:43
See if value_1 has any value which has over 10 digits in integer part

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-02 : 09:53:04
quote:
Originally posted by sqlfresher2k7

Thanks

I got an error after making the changes

CAST([Value_1] AS DECIMAL(15,5))

"Conversion failed because the data value overflowed the specified type"

That means you have values that are larger than the allowed precision in your data. To see if that is the case, run this query as a test:
SELECT MAX(CAST([Value_1] as FLOAT)) FROM YourTable
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-07-03 : 15:49:12
Thanks

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..
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-03 : 16:43:04
Please read the link below on how to post your question so we can help you better instead of playing 20 questions:

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-07-03 : 17:04:29
[code]

Here is the information.

SourcetableA:

ColumnA
-------
1202007.00000

DestinationTableB
-----------------

ColumnB
-------

Type Length Precision scale
------- ------- -------- -------
numeric 9 15 5

Expected output


ColumnB
-------
1202007.0000

[/code]
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-07-03 : 17:34:51
[code]DECLARE @SourcetableA TABLE (ColumnA DECIMAL(15, 5))
INSERT @SourcetableA (ColumnA)
VALUES (1202007.00000)


DECLARE @DestinationTableB TABLE (ColumnB DECIMAL(15, 5))

INSERT
@DestinationTableB (ColumnB)
SELECT
ColumnA
FROM
@SourcetableA

SELECT *
FROM @DestinationTableB
[/code]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 01:11:22
quote:
Originally posted by sqlfresher2k7

Thanks

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.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-07-04 : 12:06:08
[code]

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.

[/code]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 12:10:55
quote:
Originally posted by sqlfresher2k7



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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2013-07-04 : 12:47:48
[code]

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..

[/code]



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-04 : 13:37:11
quote:
Originally posted by sqlfresher2k7



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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
    Next Page

- Advertisement -