SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 06/24/2013 :  16:22:06  Show Profile  Reply with Quote

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.

James K
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 06/24/2013 :  17:13:59  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/25/2013 :  00:48:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 06/25/2013 :  09:19:18  Show Profile  Reply with Quote
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

609 Posts

Posted - 07/01/2013 :  11:51:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 07/01/2013 :  12:58:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/01/2013 :  13:34:02  Show Profile  Reply with Quote
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.. :)

Edited by - Lamprey on 07/01/2013 13:34:45
Go to Top of Page

sqlfresher2k7
Aged Yak Warrior

609 Posts

Posted - 07/01/2013 :  15:27:10  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/02/2013 :  01:57:42  Show Profile  Reply with Quote
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

609 Posts

Posted - 07/02/2013 :  09:49:36  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/02/2013 :  09:51:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 07/02/2013 :  09:53:04  Show Profile  Reply with Quote
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

609 Posts

Posted - 07/03/2013 :  15:49:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/03/2013 :  16:43:04  Show Profile  Reply with Quote
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

609 Posts

Posted - 07/03/2013 :  17:04:29  Show Profile  Reply with Quote


Here is the information.

SourcetableA:

ColumnA
-------
1202007.00000

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

ColumnB
-------

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

Expected output


ColumnB
-------
1202007.0000

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/03/2013 :  17:34:51  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/04/2013 :  01:11:22  Show Profile  Reply with Quote
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

609 Posts

Posted - 07/04/2013 :  12:06:08  Show Profile  Reply with Quote


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.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/04/2013 :  12:10:55  Show Profile  Reply with Quote
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

609 Posts

Posted - 07/04/2013 :  12:47:48  Show Profile  Reply with Quote


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





Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/04/2013 :  13:37:11  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.38 seconds. Powered By: Snitz Forums 2000