decimal division precision

in my understanding of fixed numeric types and datatype precision rules,
dividing two decimals with identical precision/scale should result in a
decimal outcome with the same precision/scale. however, i run the following
in query analyzer:
declare @.price decimal(38,10)
declare @.mult decimal(38,10)
set @.price = 5.2347551174
set @.mult = 0.01
select @.price / @.mult
RESULT> 523.475511
if i convert @.mult to a float this all works as expected. whats going on her
e?
many thanks
kh> in my understanding of fixed numeric types and datatype precision rules,
> dividing two decimals with identical precision/scale should result in a
> decimal outcome with the same precision/scale
Precision, Scale, and Length
http://msdn.microsoft.com/library/d...br />
8rc5.asp
Try using a lower precision.
declare @.price decimal(18,10)
declare @.mult decimal(18,10)
set @.price = 5.2347551174
set @.mult = 0.01
select @.price / @.mult
go
Here is a very interesting script, from Steve Kass, to see the p and s of
the result.
http://www.microsoft.com/technet/co...>
6&sloc=en-us
AMB
"kh" wrote:

> in my understanding of fixed numeric types and datatype precision rules,
> dividing two decimals with identical precision/scale should result in a
> decimal outcome with the same precision/scale. however, i run the followin
g
> in query analyzer:
> declare @.price decimal(38,10)
> declare @.mult decimal(38,10)
> set @.price = 5.2347551174
> set @.mult = 0.01
> select @.price / @.mult
> RESULT> 523.475511
> if i convert @.mult to a float this all works as expected. whats going on h
ere?
> many thanks
> kh
>|||lovely. thanks.
kh
"Alejandro Mesa" wrote:
> Precision, Scale, and Length
> http://msdn.microsoft.com/library/d... />
b_8rc5.asp
> Try using a lower precision.
> declare @.price decimal(18,10)
> declare @.mult decimal(18,10)
> set @.price = 5.2347551174
> set @.mult = 0.01
> select @.price / @.mult
> go
> Here is a very interesting script, from Steve Kass, to see the p and s of
> the result.
> http://www.microsoft.com/technet/co...
c46&sloc=en-us
>
> AMB
>
> "kh" wrote:
>|||Another way to make it work, without changing the precision of your base
numbers...
Specifically cast your result, either in your SQL statement, or assign it to
a variable which has the proper precision.
declare @.price decimal(38,10)
declare @.mult decimal(38,10)
declare @.Result decimal(38,10)
set @.price = 5.2347551174
set @.mult = 0.01
set @.Result = @.price / @.mult
select @.price / @.mult
, cast(@.price / @.mult as decimal(38,10))
, @.Result
go
"kh" <kh@.newsgroups.nospam> wrote in message
news:92EE84D6-BEB4-419A-9054-76EF39B69608@.microsoft.com...
> lovely. thanks.
> kh
> "Alejandro Mesa" wrote:
>
rules,
a
http://msdn.microsoft.com/library/d..._da-db_8rc5.asp[
color=darkred]
of
http://www.microsoft.com/technet/co...5c46&sloc=en-us[color=darkr
ed]
rules,
a
following
on here?|||Jim,
Don't understand, the least significant digits are still truncated and
stuffed with 0s in your solution.
Anyways. Try this and check out the results. Is there a pattern that I am
not able to keep my finger on... there are only 6 places of decimal. or
rather ... I dunno.
P.S: Jim, Chance for u to fuel your ego :)
declare @.price decimal(38,10)
declare @.mult1 decimal(38,10)
declare @.mult2 decimal(38,10)
declare @.mult3 decimal(38,10)
set @.price = 5.2347551174
set @.mult1 = 0.01
set @.mult2 = 0.001
set @.mult3 = 0.0001
select @.price / @.mult1,
@.price / @.mult2,
@.price / @.mult3|||So they are...
Not sure what I was thinking when I looked at that yesterday. I would have
sworn the digits were there, but clearly they are not.
I guess the moral of the story is not simply casting everything when you do
math in SQL Server, but insuring that you only use the precision you
actually need.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:651C58FC-11D3-480D-A185-DDC6CDB1E5DF@.microsoft.com...
> Jim,
> Don't understand, the least significant digits are still truncated and
> stuffed with 0s in your solution.
> Anyways. Try this and check out the results. Is there a pattern that I am
> not able to keep my finger on... there are only 6 places of decimal. or
> rather ... I dunno.
> P.S: Jim, Chance for u to fuel your ego :)
> declare @.price decimal(38,10)
> declare @.mult1 decimal(38,10)
> declare @.mult2 decimal(38,10)
> declare @.mult3 decimal(38,10)
>
> set @.price = 5.2347551174
> set @.mult1 = 0.01
> set @.mult2 = 0.001
> set @.mult3 = 0.0001
>
> select @.price / @.mult1,
> @.price / @.mult2,
> @.price / @.mult3
>