1

For the below calculation, the expected result is -0.0000671. But the result of the code is -0.00006800000.

declare 
    @V_A        decimal (38,11) = 99.99329,
    @V_B        decimal (38,11) = 100,
    @V_RESULT   decimal (38,11);

    print '@V_A = '+cast(@V_A as varchar) --Printed as "99.99329000000"
    print '@V_B = '+cast(@V_B as varchar) --Printed as "100.00000000000"
    SET @V_RESULT = (@V_A / @V_B) - 1;
    print '@V_RESULT = '+cast(@V_RESULT as varchar) --Printed as "-0.00006800000"

Interestingly, the below code returns exact result as expected.

select (99.99329 / 100) - 1  --Returns "-0.000067100".

Could you please help me to get the same output in the above T-SQL code? I don't want to use float as it will increase decimal places. Thanks in advance.

Environment: Azure SQL DB, DTU based.

Iniyavan
  • 61
  • 1
  • 5
  • Does this answer your question? [Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) – Progman Apr 28 '22 at 18:15
  • select (99.99329000000 / 100) is not 100.00 it is like select (99.99329000000 / cast(100.00 as int) ) so it looks like an int - the precision seems to increase when the int is auto cast back to what is needed to do the math. – Sql Surfer Apr 28 '22 at 18:27
  • @Progman Unfortunately No. – Iniyavan Apr 29 '22 at 09:26
  • @SqlSurfer I casted both values. But still the same issue. ```select (cast(99.99329 as decimal(38,11))/ cast(100 as decimal(38,11))) - 1``` – Iniyavan Apr 29 '22 at 09:26

1 Answers1

0

Casting to float during calculation resolves the issue.

    @V_A        decimal (38,11) = 99.99329,
    @V_B        decimal (38,11) = 100,
    @V_RESULT   decimal (38,11);

    print '@V_A = '+cast(@V_A as varchar)  --Printed as "99.99329000000"
    print '@V_B = '+cast(@V_B as varchar)  --Printed as 100.00000000000
    SET @V_RESULT = (cast(@V_A as float) / cast(@V_B as float)) - 1;  --Printed as -0.00006710000
    print '@V_RESULT = '+cast(@V_RESULT as varchar)
Iniyavan
  • 61
  • 1
  • 5