0

The above query is giving 1.0 as ouput in MS Server. But it gives 1.4 for power(2.0, 1.0/2). I really appreciate it if someone explains the reason for it. Thanks in advance!

Moni
  • 1
  • 3
    1.4 http://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=e74f88978af2364049828cd2354997b3 – McNets May 01 '17 at 17:41
  • 2
    Possible duplicate of [Division with Aggregate Functions in SQL Not Behaving as Expected](http://stackoverflow.com/questions/14326435/division-with-aggregate-functions-in-sql-not-behaving-as-expected) – GSerg May 01 '17 at 17:46

2 Answers2

5

1/2 uses integer division, which becomes zero. Two to the zeroth power is one.

James Cronen
  • 5,715
  • 2
  • 32
  • 52
  • in order to get 1.4,what is the change? – Sajith Herath May 01 '17 at 17:45
  • 2
    @SajithHerath You'll need to make at least one of the divisors a floating point number. So try `power(2.0, 1.0/2)` or `power(2.0, 0.5)`. – James Cronen May 01 '17 at 17:46
  • 1
    More surprising is that the result of `power(2.0, 0.5)` is `1.4` as opposed to a more accurate approximation of the square root of 2 -- it is documented why this is so (the output is converted to the type of the input) but not obvious, as `POWER` is nominally a function operating on `float` expressions. – Jeroen Mostert May 01 '17 at 17:56
  • 1
    Compare `POWER(2e0, 0.5)`, where the first operand really is a `FLOAT` and not a `DECIMAL(2,1)`. – Jeroen Mostert May 01 '17 at 18:04
0

Because 1 and 2 are the integer, then result converted to the integer. Like as CAST(0.5 AS INT) = 0

If least one of them had a decimal like "1.0/2" or "1/2.0" or "1.0/2.0", then result converted to decimal and result would be 0.5.

Serkan Arslan
  • 13,158
  • 4
  • 29
  • 44