A Problem with POWER()

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.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

3 Responses to A Problem with POWER()

  1. Pingback: Casting Constants And POWER() – Curated SQL

  2. Hi Steve, does SELECT POWER(2.0, n) solve the issue also?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.