September 18, 2025 at 6:14 am
I am using Microsoft SQL Server 2022 (RTM-CU17) (KB5048038) - 16.0.4175.1 (X64) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2022
Facing issue in rounding off where this query is retuning 0.0000170000 instead of 0.00001672. Actual datatype is numeric(28,8), tried increasing precision - changing data type (which i cannot do in production) to Decimal but for testing purpose tried this, using cast function but its not returning the expected value of 0.00001672
declare @requested_qty numeric(30,10) = 0.00001672,
@conv_fact_nr numeric(30,10) =1,
@conv_fact_dr numeric(30,10) = 1
Select @requested_qty = @requested_qty * (@conv_fact_nr /@conv_fact_dr)
Select @requested_qty '@requested_qty'
If SET NUMERIC_ROUNDABORT ON; is Set, then facing error - arithmetic overflow converting data type to numeric error is occurring.
Can you please suggest.
The variable @conv_fact_nr is not always 1 and is derived based on conversion factor
September 18, 2025 at 5:49 pm
I don't think this is a problem with "rounding" but a problem with "precision". SQL has complicated logic around precision. The below is taken from here
The following table defines how the precision and scale of the result are calculated when the result of an operation is of type decimal. The result is decimal when either:
Both expressions are decimal.
One expression is decimal and the other is a data type with a lower precedence than decimal.
The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that isn't decimal is the precision and scale defined for the data type of the expression. The function max(a, b) indicates to take the greater value of a or b. Similarly, min(a, b) indicates to take the smaller value of a or b.
OperationResult precisionResult scale 1
e1 + e2max(s1, s2) + max(p1 - s1, p2 - s2) + 1max(s1, s2)
e1 - e2max(s1, s2) + max(p1 - s1, p2 - s2) + 1max(s1, s2)
e1 * e2p1 + p2 + 1s1 + s2
e1 / e2p1 - s1 + s2 + max(6, s1 + p2 + 1)max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2max(s1, s2) + max(p1 - s1, p2 - s2)max(s1, s2)
e1 % e2min(p1 - s1, p2 - s2) + max(s1, s2)max(s1, s2)
The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, it's reduced to 38, and the corresponding scale is reduced to try to prevent truncating the integral part of a result. In some cases such as multiplication or division, scale factor isn't reduced, to maintain decimal precision, although the overflow error can be raised.
In addition and subtraction operations, we need max(p1 - s1, p2 - s2) places to store the integral part of the decimal number. If there isn't enough space to store them (that is, max(p1 - s1, p2 - s2) < min(38, precision) - scale), the scale is reduced to provide enough space for the integral part. The resulting scale is min(precision, 38) - max(p1 - s1, p2 - s2), so the fractional part might be rounded to fit into the resulting scale.
In multiplication and division operations, we need precision - scale places to store the integral part of the result. The scale might be reduced using the following rules:
The resulting scale is reduced to min(scale, 38 - (precision-scale)) if the integral part is less than 32, because it can't be greater than 38 - (precision-scale). The result might be rounded in this case.
The scale isn't changed if it's less than 6 and if the integral part is greater than 32. In this case, an overflow error might be raised if it can't fit into decimal(38, scale).
The scale is set to 6 if it's greater than 6 and if the integral part is greater than 32. In this case, both the integral part and scale would be reduced and resulting type is decimal(38, 6). The result might be rounded to 7 decimal places, or the overflow error is thrown if the integral part can't fit into 32 digits.
Didn't format the greatest, but basically, it's a precision/scale issue. The problem is when the math for calculating the precision and scale is done, you are getting numbers that are too large, so it is going to default of numeric(38,6). So you need to tweak things to get better values to make things fit.
Now, don't take my word for it - test it:
declare @requested_qty numeric(30,10) = 0.00001672,
@conv_fact_nr numeric(30,10) =1,
@conv_fact_dr numeric(30,10) = 1
SELECT @requested_qty * (@conv_fact_nr /@conv_fact_dr) AS RESULT,
SQL_VARIANT_PROPERTY((@requested_qty * (@conv_fact_nr /@conv_fact_dr)), 'BaseType') AS DataType,
SQL_VARIANT_PROPERTY(@requested_qty * (@conv_fact_nr /@conv_fact_dr), 'Precision') AS DataType,
SQL_VARIANT_PROPERTY(@requested_qty * (@conv_fact_nr /@conv_fact_dr), 'Scale') AS DataType
The resulting datatype after the multiplication and division works out to be Numeric(38,6). If you take 6 decimal places in your result, you get 0.000017. If you need more precise scale, you'll need to adjust the data types to handle less precision or more scale. One way to get better precision would be to use a variable to store the conv_fact value and adjust the scale on the requested_qty with something like this:
declare @requested_qty numeric(30,10) = 0.00001672,
@conv_fact_nr numeric(30,10) =1,
@conv_fact_dr numeric(30,10) = 1,
@conv_fact NUMERIC(10,5)
SELECT @conv_fact = @conv_fact_nr/@conv_fact_dr
SELECT @requested_qty * (@conv_fact_nr /@conv_fact_dr) AS RESULT,
SQL_VARIANT_PROPERTY(@requested_qty * (@conv_fact_nr /@conv_fact_dr), 'BaseType') AS DataType,
SQL_VARIANT_PROPERTY(@requested_qty * (@conv_fact_nr /@conv_fact_dr), 'Precision') AS DataType,
SQL_VARIANT_PROPERTY(@requested_qty * (@conv_fact_nr /@conv_fact_dr), 'Scale') AS DataType,
@requested_qty * @conv_fact AS RESULT,
SQL_VARIANT_PROPERTY(@requested_qty * @conv_fact, 'BaseType') AS DataType,
SQL_VARIANT_PROPERTY(@requested_qty * @conv_fact, 'Precision') AS DataType,
SQL_VARIANT_PROPERTY(@requested_qty * @conv_fact, 'Scale') AS DataType
You can see that the first 3 columns give you the original value (numeric(38,6)), whereas by storing the value for the conversion factor in a variable before you use it with appropriate precision gives you the result you expect. The reason being that the precision and scale of the variable I am using is able to make things fit in with the rules for SQL server. That being said, there are some "gotchas" here - if you need the precision and scale of the conversion factor to be more precise, you may hit the snag again where the calculated precision minus the scale works out to be larger than 32, in which case you are back to the numeric(38,6) result.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 19, 2025 at 8:23 am
This tweak seems to solve the issue
Select @requested_qty = CAST(@requested_qty as float) * (@conv_fact_nr /@conv_fact_dr)
September 19, 2025 at 7:39 pm
I'd be hesitant to use floats personally. Main reason is that floats and reals are approximate values. See this link
PLUS if you are REALLY needing a NUMERIC(30,10), the CAST to FLOAT is going to butcher the number.
For the issue about approximate values, try running this:
DECLARE @f1 FLOAT = 0.1,
@f2 FLOAT = 0.2,
@f3 FLOAT= 0.3
SELECT CASE WHEN @f1 + @f2 = @f3 THEN 1 ELSE 0 END
You'd expect a result of "1", but nope - it's 0 due to the numbers being approximate values. So 0.1 plus 0.2 doesn't equal 0.3.
ALTERNATELY, if float is the solution you want to go with, why not declare @requested_qty as a float rather than casting it? Is there a reason you need to cast it first and lose precision and potentially get VERY wrong results? Try running this:
DECLARE @test NUMERIC(30,10) = 12345678901234567890.1234567890
SELECT @test
SELECT @test = CAST(@test AS FLOAT)
SELECT @test
That cast to FLOAT is resulting in VERY different results... IF NUMERIC(30,10) is required, I'd be VERY hesitant to do the cast to FLOAT...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 20, 2025 at 9:32 am
When your factor is numeric with not enough precision same as other numbers, truncation happens. I too faced this type of problem long back. Converting the factor to Float resolves this problem.
declare @requested_qty numeric(30,10) = 0.00001672,
@conv_fact_nr numeric(30,10) =1,
@conv_fact_dr numeric(30,10) = 1
Select @requested_qty = @requested_qty * (Convert(Float,@conv_fact_nr) /Convert(float,@conv_fact_dr))
Select @requested_qty '@requested_qty'
September 25, 2025 at 8:48 am
Hi,
You can try to reduce the precision on the second part of the equation, if that's possible for the expected values on your database model, in order for SQL to increase the scale for the result, like this
declare @requested_qty numeric(30,10) = 0.00001672,
@conv_fact_nr numeric(30,10) = 1,
@conv_fact_dr numeric(30,10) = 1
Select @requested_qty = @requested_qty * Convert(numeric(16, 10), @conv_fact_nr / @conv_fact_dr)
Select @requested_qty '@requested_qty'
Best regards
Pedro
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply