Arithmetic overflow error for type int, value = 4957500001.400178

Question

I know that similar questions have been asked again in the past, but I think my case is slightly different. I have a column which has Logarithmic values and I'm trying to invert them using the following formula: SELECT POWER(10,CAST(9.695262723 AS NUMERIC(30,15)))

Let's say the value 9.695262723 is one of the values of that column. When trying to run this query I get an Arithmetic overflow error for type int, value = 4957500001.400178.

On the other hand, the same query works fine for smaller values e.g. SELECT POWER(10,CAST(8.662644523 AS NUMERIC(30,15)))

How could I overcome that error and calculate the inverse values of the log10 entries I have? Just for information the greater value that exists in the table (in log10 scale) is 12.27256096.

Answer 1

The problem here is your first input parameter (10) which SQL server will, by default, treat as the datatype int.int has a maximum value of 2^31-1 (2,147,483,647), and the number 4,957,500,001 is far larger than this, so you need to use a bigint:

SELECT POWER(CONVERT(bigint,10),CONVERT(numeric(30,15),9.695262723));

If you need to retain the decimal places, then use a numeric with a large enough scale and precision, instead of bigint.