CODEWITHSUNDEEP
sql-serveroracle-database
Luis Pavez
Luis Pavez

Reputation: 43

Same query different result in Oracle and SQL Server

I have 2 identical queries, but different results. For me the correct result is the Oracle, but I need execute this in SQL Server

SQL Server:

select power((1 + (power(1 + 0.05, 1 / 365)) - 1), 50) - 1

RESULTADO
0.00

ORACLE

select power(1+(power(1+0.05,1/365)-1),50)-1 from dual

RESULTADO
0,00670596912409796087090820980679344256

Why is this happening?

Upvotes: 2

Views: 253

Answers (2)

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

The numbers in your SQL Server expression are integer or decimal constants. You need to make some of them float constants, so that the result is float too.

You can do it like this:

select power((1 + (power(1 + 5E-2, 1 / 365E0)) - 1), 50) - 1

with the result:

0,00670596912411203

You can verify the type of expression using SQL_VARIANT_PROPERTY:

select SQL_VARIANT_PROPERTY ( 1 / 365E0, 'BaseType'), 
       SQL_VARIANT_PROPERTY ( 1 / 365E0, 'Precision'),
       SQL_VARIANT_PROPERTY ( 1 / 365, 'BaseType'), 
       SQL_VARIANT_PROPERTY ( 1 / 365, 'Precision'),
       SQL_VARIANT_PROPERTY ( 0.05, 'BaseType'), 
       SQL_VARIANT_PROPERTY ( 0.05, 'Precision')

Upvotes: 1

Juless-Z
Juless-Z

Reputation: 196

In the SQL case your using only two "0" after the "."

Try to use more then 2. should give you the same result after that.

:)

Upvotes: 2

Related Questions