How to get Microsoft SQL MATH POWER to show as decimal and not as INT (which it seems to do)?

Question

I have this simple query:

    SELECT POWER(( 1 + 3 / 100 ), ( 1 / 365 ))

According to MS SQL POWER(( 1 + 3 / 100 ), ( 1 / 365 )) = 1 when in fact it's 1,000080986

How to get Power to return Decimal instead of int. This topic seems similar to http://stackoverflow.com/questions/429165/raising-a-decimal-to-a-power-of-decimal but that one touches c# and i need SQL. If there's no other way i'll use c# way.

Edited post to just show the problem without any specific information.

Answer 1

Try:

SELECT POWER(( 1.0 + 3.0 / 100 ), ( 1.0 / 365 )) 

An expression like this 1 + 3 / 100 implicitly uses integer arithmetic, so 3 / 100 is zero, as the decimal part is truncated.

Answer 2

SELECT POWER(( 1 + 3.0 / 100 ), ( 1 / 365.0 ))

The same would happen in any (mainstream) language. Simple operator precedence issue and integer arithmetic. Nothing else

• 1/365 = 0
• Anything to power zero = 1
• And the left hand side of 1 + 3 / 100 would also equal one

Answer 3

The precision is lost because your input values are all integers.

Try

SELECT POWER(( 1.0 + 3.0 / 100.0 ), ( 1.0 / 365.0 ))

If this doesn't give sufficient precision, cast the inputs to POWER as floats:

SELECT POWER(( CAST(1.0 as float) + CAST(3.0 AS float) / 100.0 ), ( 1.0 / 365.0 ))