Power function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Power function

Author

Topic

somal
Starting Member

1 Post

Posted - 2005-02-10 : 17:47:09

I am trying to use power() function in sql 2000 it is giving strange results.i played little bit changing data types but still not working.when i use the same sql query in oracle it is working fine.

sql query used in oracle is:

select Power(1.06,(28/365)) * 1223.88 from dual
result is 1229.36292

in sql 2000:

select Power(1.06,(28/365)) * 1223.88
result is: 1223.88

So i tried following code also but it is not working:
declare @val1 float
declare @val2 float
declare @val float
declare @final as real
select @val1=28
select @val2=365

select @val= (@val1 / @val2)
select @val
select convert(varchar(15), @val)
--select convert(float,convert(varchar(15), @val))

select power(1.06, convert(real,convert(varchar(15), @val)))*1223.88
select power(1.06,@val)*1223.88

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-02-10 : 21:04:34

select Power(1.06,(28/365)) * 1223.88
result is: 1223.88

Your exponent in the power function (28/365) is evaluating to zero. Since both the numerator and the denominator are integers, an integer division is performed and the quotient truncated. Let's explicitly specify that the denominator is a DECIMAL constant by adding a decimal place.

Re-write #1

Power(1.06,(28/365.0)) * 1223.88

Now your Power function is still returning 1.0 as the result which (surprise, surprise) results in the mutiplication returning 1223.88. This is because rounding based on the assumed scale and precision of the numbers used.

This time let's explicitly specify the scale and precision of the constant 1.06.

Re-write #2

Power(cast(1.06 as decimal(15, 12)), (28/365.0)) * 1223.88

This still isn't precisely the same as the Oracle output but do you know if that output is mathmatically accurate and to how many decimal places?

You can adjust the scale and precision and you'll get slightly different answers because of the round-off errors.

HTH

=================================================================
Egotism is the anesthetic that dulls the pain of stupidity. -Frank William Leahy, football coach (1908-1973)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-10 : 21:28:33

The power function sets the output to the same precision as the first argument,
Ths second argument needs to be precise enough for 28/356 so this works:


select 	Power(
	  convert(decimal(15,8),1.06)
	  ,(28/convert(decimal(15,8), 365))
	  )
	* 1223.88

edit-
returns: 1229.362921

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-11 : 01:32:44

Try to keep first number to 8 decimal places

select Power(1.06000000,(28.00/365.00)) * 1223.88

Madhivanan

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-02-11 : 03:54:43

Looks like your doing some interest or yield calculations. You are aware that SQL is not a computational language? It doesn't have such a good math library as other client languages such as C++ have. See if this helps explaining:
http://groups.google.co.uk/groups?q=7450580596923828125+kass&hl=en&lr=&selm=ePR4uA7AFHA.4028%40TK2MSFTNGP15.phx.gbl&rnum=1

--
Frank
http://www.insidesql.de

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources