Rounding off problrm - 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)

Rounding off problrm

Author

Topic

DMP
Starting Member

41 Posts

Posted - 2005-12-08 : 02:15:24

Declare @A Numeric(28,10),@B Numeric(28,10)
Select @a=1000,@B=.3333333334
Select @a*@b

Why the Result is 333.333333 instead of 333.3333334000 ?
What is Result value's Scale , when i multiply two or more Numeric varriables/Fields ?
Regards

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-08 : 02:35:06

mmmm.... Interesting.

This does seem to work though:

Declare @A numeric(28,20),@B numeric(28,20)
Select @a=1000,@B=0.3333333334
Select @a*@b

Duane.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-08 : 02:45:57

or this also works

Declare @a Numeric(25,10),
	@b Numeric(25,10)

Select @a =1000, @b =.3333333334
Select @a, @b, @a * @b

anybody can explain why ?

-----------------
[KH]
Dec-05

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-12-08 : 03:05:34

mmmm... seems to start behaving funny when larger precision is used.

--*** This works
declare @a numeric(9,5), @b numeric(9, 5)
select @a = 1000, @b=.33334
select @a * @b
go

--*** This works
declare @a numeric(19, 10), @b numeric(19, 10)
Select @a=1000,@B=0.3333333334
select @a * @b

go
--**** This does not
declare @a numeric(28, 10), @b numeric(28, 10)
Select @a=1000,@B=0.3333333334
select @a * @b
go

--*** This works ????
declare @a numeric(25, 10), @b numeric(25, 10)
Select @a=1000,@B=0.3333333334
select @a * @b
go

--**** This does not
declare @a numeric(36, 10), @b numeric(36, 10)
Select @a=1000,@B=0.3333333334
select @a * @b
go

--**** This does not
declare @a numeric(32, 10), @b numeric(32, 10)
Select @a=1000,@B=0.3333333334
select @a * @b

Duane.

DMP
Starting Member

41 Posts

Posted - 2005-12-08 : 03:44:11

Thanks,
But have any proper explanation ?

Kristen
Test

22859 Posts

Posted - 2005-12-08 : 04:55:59

SELECT @@MAX_PRECISION ??

BoL has a table of the precision used in calculations, which is based on the precision of each operand.

"Precision, Scale, and Length"

but even having read that I'm still not sure why its not giving more precision in the result.

Kristen

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2005-12-12 : 22:28:36

From BOL

quote:
NUMERIC_ROUNDABORT

If set to ON, an error is generated when loss of precision occurs in an expression. When set to OFF, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result.

SET NUMERIC_ROUNDABORT must be set to OFF when you create or manipulate indexes on computed columns or indexed views.

The status of this option can be determined by examining the IsNumericRoundAbortEnabled property of the DATABASEPROPERTYEX function.

If you do

SET NUMERIC_ROUNDABORT ON

Declare @A Numeric(28,10),@B Numeric(28,10)
Select @a=1000,@B=.3333333334
Select @a*@b

you will get the message that

Server: Msg 8115, Level 16, State 1, Line 5
Arithmetic overflow error converting numeric to data type numeric.

This is because when the two numerics are multiplied, the result would not fit into the maximum numeric type precision @@MAX_PRECISION because 28+28 = 56 (ie overflow).

So you should either perform your calculation in float, and then convert your answer back to numeric

Declare @A Numeric(28,10),@B Numeric(28,10)
Select @a=1000, @B=.3333333334
Select cast(cast(@a as float) * cast(@b as float) as numeric(28,10))

which produces: 333.3333334000 (and no error).

Or, alternatively, reduce your precision so that the multiplication will produce a value which does not require more than a precision of @@MAX_PRECISION, eg

Declare @A Numeric(18,10),@B Numeric(18,10)
Select @a=1000, @B=.3333333334
Select @a*@b

which gives the answer

333.3333334000000000000

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

DMP
Starting Member

41 Posts

Posted - 2005-12-13 : 07:05:24

Thanks rrb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources