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?

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

Answer 2

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.

Answer 3

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