Decimal points - is this a SQL glitch?-resolved

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 2005 Forums

Transact-SQL (2005)

Decimal points - is this a SQL glitch?-resolved

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-17 : 20:12:44

Why does this still round the decimal points, and not use the 35 places? Is there anyway to raise to the power of something and keeping the decimal places?

Here is a sample

select power(convert(decimal(38,35),1.000) / 3.00,2)
select (convert(decimal(38,35),1.000) / 3.00) * (convert(decimal(38,35),1.000) / 3.00)

Basically why are the first query and the second query giving me differant decimal results?


/*This shows me i am getting the 0.04409771280524280 # in the query, but why when I multiply it am I getting differant results?*/

select (select (power(1 + convert(decimal(38,30),.045),convert(decimal(38,30),1) / 12) -1) * 12)

select convert(Decimal(38,30),(select (power(1 + convert(decimal(38,30),.045),convert(decimal(38,30),1) / 12) -1) * 12) * convert(decimal(38,30),123213.4444))
-- Why is the below query giving differant results than the above?
select convert(decimal(38,30),0.04409771280524280) * convert(Decimal(38,30),123213.4444)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-18 : 08:11:30

SQL mag had an excellent article on this year a few years ago, too bad they're subscription only. BOL has some hints, tho. From Precision, Scale and Length (referenced by the SQL Mag article)

The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Basically, the gist was that a precision of 20 times a precision of 20 needs a precision of 40 to "think" with before returning a precision of 20, so something's gotta go.

Jim

http://www.sqlmag.com/Article/ArticleID/45921/sql_server_45921.html

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-18 : 08:19:10

I get the precision factor, but my question is why are the results differant if the exact same #'s are being used, and they're the same typ/precision? Please see code above to illustrate.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-06-18 : 09:47:23

quote:
Originally posted by Vinnie881

I get the precision factor, but my question is why are the results differant if the exact same #'s are being used, and they're the same typ/precision? Please see code above to illustrate.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

They are not the same data type internally during the calculation.

The POWER function is a floating point operation internally, and then converts the result back to the original datatype. You can see what happens with the following examples.



select A = 1.000E/9.000E
select B = power(1.000E/3.000E, 2.0E)
select C = convert(decimal(38,35),power(1.000E/3.000E, 2.0E))
select D = power(convert(decimal(38,35),1.000) / 3.000,2)


Results:
A                                                     
----------------------------------------------------- 
0.1111111111111111


B                                                     
----------------------------------------------------- 
0.1111111111111111


C                                        
---------------------------------------- 
.11111111111111110000000000000000000


D                                        
---------------------------------------- 
.11111111111111110000000000000000

CODO ERGO SUM

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-18 : 11:12:00

I am getting very confused. Can you please explain a little more.

If I run this query
select (power(1 + convert(decimal(38,30),.045),convert(decimal(38,30),1) / 12) -1) * 12

it gives me

--RESULTS
0.0440977128052428

so if that is what the query returns as a result of the first part, shouldn't the below 2 queries return the EXACT same results? If not can you please explain how I would with the greatest accuracy?


select convert(Decimal(38,30),(select (power(1 + convert(decimal(38,30),.045),convert(decimal(38,30),1) / 12) -1) * 12) * convert(decimal(38,30),123213.4444))

Select convert(decimal(38,30),0.04409771280524280) * convert(Decimal(38,30),123213.4444)

I'm sure you already answered this, in your post, but it still is not making sense to me and would appriciate a little more clarification.

Thank you.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-06-18 : 12:08:28

quote:
Originally posted by Vinnie881
...shouldn't the below 2 queries return the EXACT same results? If not can you please explain how I would with the greatest accuracy?

select convert(Decimal(38,30),(select (power(1 + convert(decimal(38,30),.045),convert(decimal(38,30),1) / 12) -1) * 12) * convert(decimal(38,30),123213.4444))

Select convert(decimal(38,30),0.04409771280524280) * convert(Decimal(38,30),123213.4444)

...

You misplaced a ) so that both calculations do not have the same order of datatype conversion.

With the small change below in the first calculation, you get identical results.


select A = convert(Decimal(38,30),(select (power(1 + convert(decimal(38,30),.045),convert(decimal(38,30),1) / 12) -1) * 12)) * convert(decimal(38,30),123213.4444)

Select B = convert(decimal(38,30),0.04409771280524280) * convert(decimal(38,30),123213.4444)


Results:
A                                        
---------------------------------------- 
5433.431084895951766300320

B                                        
---------------------------------------- 
5433.431084895951766300320

CODO ERGO SUM

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-18 : 13:32:29

ahhhhh!!!!! so simple, yet so frustrating.

Thank you very much!

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources