'SQL Server - Decimal values not precisely calculated
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.
Solution 1:[1]
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)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Iniyavan |
