Calculating with Decimal data types

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

Transact-SQL (2000)

Calculating with Decimal data types

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-25 : 14:07:16

Jure writes "Hi!

I have a problem with calculations. There's example of what is going wrong:

---------------------------------------------------
Declare @A Decimal(38,28),
@B Decimal(38,28),
@C Decimal(38,28)

Set @A = 10
Set @B = 3

Set @C = @A / @B

Print @C
----------------------------------------------------

Result:
3.3333330000000000000000000000
but it should be:
3.3333333333333333333333333333

Can someone tell me how does this happen??

- Jure"

SamC
White Water Yakist

3467 Posts

Posted - 2003-04-25 : 15:11:47

Interesting with BOL has to say about decimal precision:
Operation Result precision Result scale *
e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 * e2 p1 + p2 + 1 s1 + s2
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)

* 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.

If I read this right, dividing a decimal into a decimal results in a precision (in your case) of:

precision = 38 - 28 + 28 + max(6, 28 + 38 + 1) = 103

The (*) above says that the scale (28 in your case) is reduced if the precision in your calculation is greater than 38.

This may have something to do with the problem you are experiencing.

Very curious. I don't completely understand this and look forward to any comments.

Sam

A not too complete improvement on the results is:

set @c = cast(@a as float(53)) / cast(@b as float(53))

but this gives 17 digit precision, not 38

Edited by - SamC on 04/25/2003 15:17:38

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-25 : 15:21:30

Well I tried something without repeating decimals...pi

Declare @A Decimal(2,0), @B Decimal(1,0), @C Decimal(29,28)
SELECT @A = 22 , @B = 7
SELECT @C = @A / @B
SELECT @C

And got:

3.1428570000000000000000000000

(1 row(s) affected)

I have no idea

EDIT: Is it a server setting maybe?

Brett

8-)

Edited by - x002548 on 04/25/2003 15:22:53

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-25 : 15:25:22

Brett -- you know PI isn't really 22/7, right? ....

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-25 : 15:35:46

Nope, didn't realize that. Is it just used as an approximation:

http://mathforum.org/library/drmath/view/52543.html

Thanks for the insight.

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-25 : 15:55:37

SamC has it.

Your result precision is > 38 so the scale is reduced to a min of 6.

If you reduce the precision of the inputs you will get more scale.

Declare @A Decimal(20,12),
@B Decimal(20,12),
@C Decimal(38,28)

Set @A = 10.00000000
Set @B = 3.00000000

Set @C = @A / @B
select @C

3.3333333333333333330000000000

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-25 : 16:08:19

precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
scale = max(6, s1 + p2 + 1)

if we divide by an dec(1,0)
precision = p1 - s1 + s1 + 2
scale = s1 + 2

so to get a dec(38,28)
s1 = 26, p1 = 36
and

Declare @A Decimal(36,26),
@B Decimal(1,0),
@C Decimal(38,28)

Set @A = 10
Set @B = 3

Set @C = @A / @B
select @C

3.3333333333333333333333333333

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-04-25 : 16:52:06

More stuff here:
http://mathworld.wolfram.com/Pi.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources