1

Could anybody explain what happens in the following sql code?

declare @dividend numeric(38,22)

declare @divisor numeric(38,22)

declare @otherDivisor int

set @dividend = 1

set @divisor = 3

set @otherDivisor = 3

select cast(@dividend / @divisor as numeric(38,22)), @dividend / @otherDivisor

The result returned is

0.3333330000000000000000 0.3333333333333333333333

I would expect the same result for both calculations.

Community
  • 1
  • 1
ralf
  • 13
  • 3

3 Answers3

0

Actually I take that answer back. It looks as though SQL is coercing the result to whichever has the higher precedence, dividend or divisor. 

      select 1.00000000000000000000/3.0
      select 1.0/3.0
Tim
  • 8,669
  • 31
  • 105
  • 183
0

Try this:

select cast(@dividend as numeric(38,22)) / @divisor, @dividend / @otherDivisor

You are casting after doing the division.

Victor Parmar
  • 5,719
  • 6
  • 33
  • 36
0

decimal(38,22) / decimal(38,22) ends up with decimal(x, 6) following these rules

So you have 0.33333 before you cast back to decimal(38,22)

@otherDivisor is cast to (38, 0) and stays as decimal(x,22)

See my worked example

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Thank you very much for the answer. Are you aware of any way to work arround this behaviour? – ralf Dec 16 '10 at 15:35
  • @Ralf: It follows normal maths rules, just like multiplying decimal places in real life (2dp x 3 dp gives 6 dp). Float may be better, at the expense of some accuracy... – gbn Dec 16 '10 at 15:55