0

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 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.

Community
  • 1
  • 1
MadBoy
  • 10,824
  • 24
  • 95
  • 156

3 Answers3

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 ))
Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • Thanks, precision is important so that's why I'm choosing your answer as accepted one. – MadBoy Feb 04 '10 at 11:56
  • @MadBoy: Precision with float...? Just cast to decimal (38, 19) or something – gbn Feb 04 '10 at 12:03
  • I should have been more precise. Precision up to 8 places is important, so i will be cutting out what float returned anyways. – MadBoy Feb 04 '10 at 12:27
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.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
1
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
gbn
  • 422,506
  • 82
  • 585
  • 676
  • That is also incorrect; you are still doing integer arithmetic (and gettig truncation) when you do 3 / 100. Ah! you edited it! – Mitch Wheat Feb 04 '10 at 11:51
  • @Mitch Wheat: yes, / evaluated before + Serves me right for rushing – gbn Feb 04 '10 at 11:53