SQL Server can do some intense Mathematical calculations. Following are three very basic and very necessary functions. All the three function does not need explanation. I will not introduce their definition but will demonstrate the usage of function.
SELECT PI()
GO
SELECT POWER(2,5)
GO
SELECT POWER(8,-2)
GO
SELECT EXP(99)
GO
SELECT EXP(1)
GO
Results Set :
PI
———————-
3.14159265358979
PowerEg1
———–
32
PowerEg2
———–
0
ExpEg1
———————-
9.88903031934695E+42
ExpEg2
———————-
2.71828182845905
Now the Questions asked in the Title of the Article – What is the result of EXP to the POWER of PI
SELECT POWER(EXP(1), PI())
GO
Results
———————-
23.1406926327793
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL – PI, BOL – EXP, BOL – POWER
5 Comments. Leave new
Hi,
Can anyone please help with this? I am currently trying to transpose this Excel formula to SQL Server 2005 but I can not seem to get to grips with the POWER() function – could anyone tell me where I am going wrong?
The Excel formula is sound and does not need adjusting.
Excel formula:
=((1+(5.47+1.65/100)/4)^(1/3)-1)
Result:
0.333568535
SQL Server:
SELECT POWER((1+(5.47+1.65/100)/4),3)-1
Result:
12.33945417
I Worked it out.
It looks like you can’t have a fraction as the power, so have to convert to a decimal (i.e. 0.33333)
SELECT POWER((1+(5.47+1.65/100)/4),0.33333)-1
Or: SELECT POWER((1+(5.47+1.65/100)/4),cast(1 as float)/3)-1
@Scott Spence you simply had a math error in your SQL Server example. You were applying the 3rd power, not the 1/3rd power, since you have a 3 in the second argument.
It should be SELECT POWER((1+(5.47+1.65/100)/4),1.0/3)-1
@Andrew McKee: seriously, “cast(1 as float)”? Why not just 1.0?
why does SELECT POWER(10,-.67) evaluate to zero?