CODEWITHSUNDEEP
sql-server
Axs
Axs

Reputation: 805

How to make the power() function use decimal instead of integer?

The following query throws an error:

SELECT POWER( 10, 13)

Error message:

Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value = 10000000000.000000.

Whan can I do to prevent the int overflow?

Upvotes: 2

Views: 1695

Answers (3)

Geoff
Geoff

Reputation: 8850

Add a decimal place:

SELECT POWER(10.0, 13)

That causes an implicit cast from int to DECIMAL(18,6). You could write it for identical results as:

SELECT POWER(CAST(10 as DECIMAL(18,6)), 13)

The POWER function return type is documented as:

Returns the same type as submitted in float_expression. For example, if a decimal(2,0) is submitted as float_expression, the result returned is decimal(2,0)

This means that your result is a DECIMAL(18,6), which in your case is big enough to hold the final result.

Upvotes: 4

user353gre3
user353gre3

Reputation: 2755

Use following

SELECT POWER( 10.0, 11 - LEN(9)) - 1

Explanation

Arithmetic overflow occurs when a calculation produces a result that falls outside the min & max values that a datatype can store. In your case, POWER( 10, 11 - LEN(9)) produces 10000000000, and the datatype is implicitly implemented as int. This value is greater than the max value that int can take, i.e., 2147483647, thus creating the overflow. If you use POWER( 10.0, 11 - LEN(9)) with 10.0, the calculation produces decimal, as decimal has higher precedence in SQL Server than int. Decimal has a range of - 10^38 +1 through 10^38 - 1, and so does not create overflow for your calculation.

Upvotes: 2

Tanner
Tanner

Reputation: 22743

You could use CONVERT:

SELECT CONVERT(DECIMAL(18,2), 10)

This specifies 2 decimal places, but you can modify the numbers within the brackets (18,5) to adjust this precision & scale as desired

Upvotes: 1

Related Questions