Decimal with high precision and scale creating 'Arithmetic Overflow Error'

Question

I imported a large amount of data using BCP into SQL Server that contained all strings. To keep further integrity of the db I am altering all the columns for there correct metadata.

I have a current string column that is actually full of decimal values and need to alter the column to make it decimal(p, s).

Because of the large amount of data, I wrote the following query to get the max length of the string before and after the decimal point; added the length of the string from before and after decimal to get precision. The following query will show what I arrived to:

SELECT 
    '('
    +
    CAST(MAX(LEN(LEFT(Measure, LEN(Measure) - LEN(SUBSTRING(Measure, CHARINDEX('.', Measure) , 255))))) 
    + 
    MAX(LEN(SUBSTRING(Measure, CHARINDEX('.', Measure) +1, 255))) AS varchar(5)) 
    + 
    ',' 
    + CAST(MAX(LEN(SUBSTRING(Measure, CHARINDEX('.', Measure) +1, 255))) AS varchar(5))
    +
    ')' AS 'max[p,s]'
FROM
    Result

The following is the result:

max[p,s]
--------
(24,19)

When I Alter the column by stating the following:

ALTER TABLE Result
ALTER COLUMN Measure DECIMAL (24,19)

I get the following error:

Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting varchar to data type numeric. The statement has been terminated.

(p,s) where p = precision and s=scale; I was under the impression that I can have a max precision (p) of 38. Why am I getting this error? Any suggestions? It is imperative that I don't lose any precision.

Are you sure you don't have any non-numeric values in that column? — APH
– APH, Commented Aug 27, 2015 at 20:44

Jon C · Accepted Answer · 2015-08-27 22:35:05Z

2

You can try finding the bad row(s) with this:

select Measure from Result 
where TRY_CONVERT(DECIMAL(24,19),Measure)  
is Null And Measure is Not Null;

answered Aug 27, 2015 at 22:35

Jon C

6644 silver badges11 bronze badges

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

5 Comments

Ricardo C Over a year ago

That will give false positives because the precision is incorrect.

Jon C Over a year ago

I've run a few tests with @Jeff Orris query to find the required precission and they turned to be correct. I believe there is some non-numeric value somewhere.

J.S.Orris Over a year ago

@JonC I ran the advised query and get the following 2 results: 132000 116000....why won't these convert to decimal?

Jon C Over a year ago

Apparently because it's trying to add the 19 decimal precision to those integer numbers and then you end up with a higher precision (more than 23 digits on the left and right of the decimal point). It works if you change them for 132000.0 and 116000.0 but as they don't have a floating point then it's trying to make a conversion from an integer to a decimal with the provided precision.

J.S.Orris Over a year ago

@JonC Thank you for advising to run your query. The problem is that my posted query wont work on a whole number represented as a string. So first I have to got the max len of the sting integer and then query values LIKE '%.%.' to get max len of right of decimal. I will accept your answer because this let me know what I needed to do (change to varchar(25,19)). I will add another answer that changes my posted query in my question to get the true max (p,s) needed in case there are values with no '.'.

Ricardo C · Accepted Answer · 2015-08-28 17:37:47Z

From MSDN decimal and numeric (Transact-SQL)

p (precision) The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

To get the precision use:

SELECT  MAX(LEN(Measure)) AS max_precision_needed 
FROM    Result

The above query does not subtracts the decimal point because a string could contain an integer with no decimal point at all. It gives you the possible maximum needed.

Example: The number 123.00 needs a field with the following precision; decimal(5,2)

In the following example after computing the needed precision, one could do the conversions.

   DECLARE @measures TABLE ( measure VARCHAR(50) );

   INSERT   INTO @measures( measure ) VALUES   ( '123' )
   INSERT   INTO @measures( measure ) VALUES   ( '123.00' )
   INSERT   INTO @measures( measure ) VALUES   ( '123.001' )
   INSERT   INTO @measures( measure ) VALUES   ( '.123' )
   INSERT   INTO @measures( measure ) VALUES   ( '0.123' )
   INSERT   INTO @measures( measure ) VALUES   ( '   ' )

   SELECT   '(' + CAST(MAX(LEN(measure)) - 1 AS VARCHAR(5)) + '.' + CAST(MAX(LEN(SUBSTRING(measure, CHARINDEX('.', measure) + 1, 255))) AS VARCHAR(5)) + ')'
   FROM     @measures

   SELECT   ISNULL(CONVERT(DECIMAL(6, 3), CASE WHEN CHARINDEX('.', measure) = 0 THEN measure + '.0' ELSE measure END), 0.0)
   FROM     @measures

In this example the conversion should not fail by integers in the strings or empty strings.

If you try @Jeff Orris query with your example (123.00) you get (5,2) as the required precission, his query is actually counting the digits stored to the left and right of the decimal point as you correctly stated.
The advised query will count the decimal point so p will be 1 more than needed
That is correct @JeffOrris , I added a comment to indicate that fact. Thank you.

Collectives™ on Stack Overflow

Decimal with high precision and scale creating 'Arithmetic Overflow Error'

2 Answers 2

5 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Related