I ran into an interesting problem while working with the POWER() function. I was trying to do some binary conversions and had a statement like this to process powers of 2.

SELECT POWER(2, n)

This was designed to take a value and return a power of 2. I then used a different value to determine if this was added to my conversion factor or not. In trying to work with some larger numbers, I ran into this error:

Msg 232, Level 16, State 3, Line 3

Arithmetic overflow error for type int, value = 2147483648.000000.

The error tells me I’ve exceeded the size of an integer. When I looked up the POWER() function, it tells me that it returns a bigint for a bigint input. Since I had ensured my “n” was a bigint, I was confused for a few minutes.

Then I realized that it’s not the n, but the “2” that’s the problem. By default, this scalar value is an integer. That means I need to ensure that this is a bigint to make this work. I changed to:

SELECT POWER(CAST(2 AS BIGINT),n)

And things worked.

Double check all the data types when you get a conversion error. SQL Server knows what’s wrong, but sometimes you need to dig in to determine where in your code you’ve made the mistake.