Arithmetic overflow error in SQL

Question

So just to give you an example of how our procedure would eventually work with data and come up with this error, here's some SQL:

DECLARE @daysInPeriod  INT
DECLARE @dcFraction    DECIMAL(38,38)
DECLARE @YEAR_360         DECIMAL
SET @YEAR_360 = 360.0000000000
SET @daysInPeriod = DATEDIFF(dd, '12/24/1970', '6/29/07')
SET @dcFraction = @daysInPeriod/@YEAR_360

If you run this in SQL Server 2008 R2, you'll see that you receive an 'Arithmetic overflow error converting numeric to data type numeric.' error.

The decimal that I am setting the value to is max'd out in terms of places it can hold, which I believe is 38. I don't know what else I can do to get this value. Shouldn't you be able to divide an integer with a decimal in SQL and get something out of it, especially if the decimal is max'd out?

Lamak · Accepted Answer · 2011-12-15 17:38:03Z

7

In your example, the value of @daysInPeriod/@YEAR_360 is 37.0444444444444444444. So you can't assign that value to a parameter of data type DECIMAL(38,38), since you are leaving no room the int part. A DECIMAL(38,38) means that you can store 38 digits, and 38 of them are on the decimal part, so any value greater than 0.999999999999 will throw an error.

answered Dec 15, 2011 at 17:38

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

slandau Over a year ago

Oh wow I probably should of looked up the decimal declaration first. This was in the code already so I incorrectly assumed it was written right the first time. Thanks!

Coding Flow Over a year ago

@slandau Yes you probably should but these things happen to all of us when working with other peoples code in an unfamiliar language, we assume the other guy knew what he was doing.

Joe Stefanelli · Accepted Answer · 2011-12-15 17:38:08Z

1

The problem is your declaration of @dcFraction as DECIMAL(38,38). You're saying you want all 38 digits to the right of the decimal point, with no room for the integer portion to the left.

answered Dec 15, 2011 at 17:38

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Comments

Sergey Kalinichenko · Accepted Answer · 2011-12-15 17:38:25Z

1

If you give your decimal a smaller scale, it is going to work:

DECLARE @daysInPeriod  INT
DECLARE @dcFraction    DECIMAL(38,12)
DECLARE @YEAR_360         DECIMAL
SET @YEAR_360 = 360.0000000000
SET @daysInPeriod = DATEDIFF(dd, '12/24/1970', '6/29/07')
SET @dcFraction = @daysInPeriod/@YEAR_360
select @dcFraction

This returns 37.044444444444

answered Dec 15, 2011 at 17:38

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Collectives™ on Stack Overflow

Arithmetic overflow error in SQL

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related