Thread: Arithmetic overflow error converting numeric to data type numeric

1. Registered User
Join Date
Oct 2005
Posts
37

Unanswered: Arithmetic overflow error converting numeric to data type numeric

Guys

I'm getting the above when trying to populate a variable. The values in question are :
@N = 21
@SumXY = -1303765191530058.2251000000
@SumXSumY = -5338556963168643.7875000000

When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)

Any ideas ??

2. Registered User
Join Date
Oct 2005
Posts
37
Sorry - did a typo - problem code is
SELECT @R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)
not
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Try:
SELECT R2Top = CAST((@N * @SumXY) - (@SumXSumY * @SumXSumY) as NUMERIC (38, 10))

4. Registered User
Join Date
Oct 2005
Posts
37
Thanks blindman but still same error.

If I change data type of @R2Top to FLOAT I get no error but aren't there accuracy issues with FLOAT ? I have further calculations to perform in the code and the accuracy is very important

5. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
What are the datatypes for @N, @SumXY, and @SumXSumY?

6. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
OK. Here is your problem. numeric(38, 10) only leaves you 28 digits to the left of the decimal. Your problem requires 32 digits to the left of the decimal.

This code works:
Code:
declare	@N int
declare	@SumXY decimal(26, 10)
declare	@SumXSumY decimal(26, 10)
declare	@R2Top numeric(38, 6)

set @N = 21
set @SumXY = -1303765191530058.2251000000
set @SumXSumY = -5338556963168643.7875000000

select @R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)
select	@R2Top

7. Registered User
Join Date
Oct 2005
Posts
37
Thanks blindman

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•