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.
Author |
Topic |
neo_bagsjol
Starting Member
6 Posts |
Posted - 2014-03-05 : 21:56:52
|
Hello guys i would like to ask why the result from Power function of sqlserver is different from the windows calculator for exampleSQL RESULTSELECT POWER('1.0116666666666667','120') RESULT IS: 4.02247064115812WINDOWS CALCULATOR1.0116666666666667 ^ 120RESULT IS:4.0224706411581114419518589580497Is there a way to compute result like windows calc from sqlserver cozi'm having different result when i run my script using Power function at the end of result i got -0.01 instead of 0.00i need your help please i been doing this for 2 weeks still i got negative resultthanks guys |
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-03-06 : 11:04:29
|
Can you edit your script? If yes, then limit the power portion to the same number of decimals SQL is showing. e.g CAST ... AS NUMERIC(20,14)=======================Not an Expert, Just a learner.!_(M)_! |
 |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-03-06 : 11:26:39
|
I don't think there is much you can do other than write your own Power function. The built-in function uses a float. Since that is an approximate data type, you won't get very precise results. |
 |
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-03-06 : 11:33:55
|
I was just curious so I wrote a quick loop to see what the results would be. Due to the way SQL Server handles Decimal/Numeric multiplication you need to set the scale really high to get a precise result:DECLARE @Val DECIMAL(38, 37) = 1.0116666666666667;DECLARE @Exponent INT = 120;DECLARE @Result DECIMAL(38, 37);WHILE @Exponent - 1 > 0BEGIN SET @Result = CASE WHEN @Result IS NULL THEN @Val * @Val ELSE @Result * @Val END SET @Exponent = @Exponent - 1ENDSELECT @ResultRESULT: 4.0224706411581114419518589580496877300 |
 |
|
|
|
|