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.
Pingback: Casting Constants And POWER() – Curated SQL
Hi Steve, does SELECT POWER(2.0, n) solve the issue also?
It won’t because the 2.0 is an INT as well. This needs to have a bigint as an input parameter to get a bigint out.