Solved Rounding To 16Dp Decimal (3 Different Answers)

Thanks, Doc I didn't post a very good link but the point I was trying to make is that on one of my bad e.g's of LET coercion I cannot see why SQL would convert to SINGLE/ DOUBLE when they are not recognized data-types in SQL Server. Access is not even in the equation atm but the point I was making is that it seems SQL can convert to decimal & single but I can't see why it would do that natively without Access being in the equation. This is a side topic mind, I have bigger problems in understanding the math in the basic same type DECIMAL(28,16); which is the main problem I face. I'm not even converting types, the columns in real-use are the correct types; so no cast is happening. The e.g.

if you're going to want a decimal results in t-sql make sure the inputs are decimal
Thanks Isaac, they are:

1769729178349.png


Var's are explicitly declared as the decimal to what appears to be the correct scale & depicted in my analysis of the rules. The point is it's being truncated/ rounded to 10dp as Sonic pointed out. But following the rules I cannot see where I am going wrong. In my simple mind my math appears correct. According to MSN's Rules it should display the result as pic'd from Windows Calc: as highlighted :unsure:: Hopefully someone points me to my error, else if my math is correct the rules are incorrect; it's much more likely I'm wrong, but I cannot see where.
1769729359547.png
 
But following the rules I cannot see where I am going wrong.

From the docs:
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.

SQL:
BEGIN
    DECLARE
        @e1 AS DECIMAL (28,16) = 1,
        @e2 AS DECIMAL (28,16) = 1.95;
     
-- ...

28+16 = 44
44 > 38

This could likely be the cause.

See Philipp's second code suggestion in Post #15 (NoMoreWTF)

Your result is the same as DECIMAL(28,10) - try that and you will see your output.
 
Thanks Dave, I believe we need to use the formula's as per the operation to accurately asses the result's precision & scale as per #17. I need to accurately assess because I have lots of these to do.
 
Last edited:
I believe we need to use the formula's as per the operation to accurately asses the result's precision & scale as per #17.
Are you plotting inter-planetary flight paths? :ROFLMAO: That's a lot of scale for such large numbers!

I'm not sure how you go about it.

As explained,
SQL:
    DECLARE
        @e1 AS DECIMAL (28,16) = 1,
        @e2 AS DECIMAL (28,16) = 1.95;
       
    -- is the same as:

    DECLARE
        @e1 AS DECIMAL (28,10) = 1,
        @e2 AS DECIMAL (28,10) = 1.95;

Are the whole parts of the numbers you are dealing with really going to be larger than 999,999,999,999 ?

Will the following not suffice?
SQL:
    DECLARE
        @e1 AS DECIMAL (22,16) = 1,
        @e2 AS DECIMAL (22,16) = 1.95;
 
Last edited:
Thanks, I need to pick limits to work with & they're going through several multipliers, being converted to percentages & multiplied against each other. So compounding has a massive effect very quickly after the decimal place & I'm trying to find what's going to be the best combination with these. I may be a little excessive with decimal(28,16) so i=12 (999,999,999,999) as you pointed out but it certainly needs to have an integral of > 9 & really as big as possible is best from a user-perspective. Regarding decimal places I need as much as I can get; test calc's done to 15 dp & inaccuracies noticed on very small amounts, so compounding will greatly exaggerate this.

I don't know yet & that's the point of this thread. I have a lot of work to do in deciphering these. Understanding the algebra behind the math is the topic; not the literals of a single e.g. But obv's I had to give an e.g. to apply the problem to. I'm very grateful of the help here obv's... So do not want this to sound argumentative, this is debate as to what the actual problem is. Forget meaninglessly reducing precision for a single e.g., it's about mastering this topic for the scores of storage capcities required for my app to function & LET coercion (later, struggling enough with the basics atm 🤮).


@cheekybuddha I cannot see how #22 applies. You've picked rule 1 of the very first rules. Which seems to relate to addition/ subtraction more so. As my operation is division it ambiguously refers to 'scale factor'. Obviously I'm wrong somewhere in my logic but in trying to figure it all out:
1769767941438.png

But contradicts the the absolute value of 38 (p+s) is too simplistic a total for p here. We need to deduce the Result Total dependant to exactly what operation is in effect; that's division in this case. So Result Precision, Scale & the Integral must be calc'd as so.
1769768611554.png


Seems you have deduced a single expression only; where all expressions need to be assessed dependant on what operation is in effect; which has been done in #17 (division operation).
28+16 = 44
44 > 38
Then integral needs to be deduced.
1769769044773.png

Which again is dependant to if an addition/ subtraction is used OR a multiplication/ division is in effect. Which is dependant on the s required of the result; which is dependant whether it is +/- OR *//... I think I get the gist you're getting at:

So Result P = 73
Result S = 45
Multiplication & Division Rules apply; so M&D rule-3 applies; As the Result Integral (i) is > 32 ∴ s=6 & i=32; resulting in dec(38,26) so the nr returned would be truncated to s=6 (6 decimal places) & a 32 point integral. Yet we're not seeing that; we're seeing 10 decimal places:
1769770363618.png

I've gone step by step in #17... providing a detailed computation considering all expression terms... Hopefully someone can point out where I'm wrong with the algebra. Again not wanting to offend, but stating different variations of decimal type is of little help. The topic is more complex than that. Been some awesome input which I'm very grateful of, but as people keep point out 'need to accurately assess storage requirements...'. :ROFLMAO:👏 Is what is trying to be done in #17. Again hopefully someone can point out what my error is in my logic/ math.
 
Last edited:
So Result P = 73
Result S = 45
Multiplication & Division Rules apply; so M&D rule-3 applies; As the Result Integral (i) is > 32
The integral part is: Precision - Scale.
In your case: 73 - 45 = 28
So, Rule #1 applies!
Scale = min(scale, 38 - (precision-scale))
In your case: min(45, 38-(73-45)) = 10
 
Thank you sonic, & thank you all for your help. I finally get it & never would have been able to get here without all your help. Thank you so much. To clarify Rule-1 of the Multiplication/ Division Rules. Not the very first rule 1.

1769773942402.png
 
I'm going to take a different tactic for a moment. You are looking at serious precision. However, sometimes it does more good to look ahead to the end of your journey to be sure that you aren't wasting your time. Or making more work for yourself at the point when your computations are done and the real world steps into the picture.

So... you have these numbers computed to extreme precision. What are you going to do with them? Let me explain the point I want to make. If these numbers go to a bank, what is the limit on what they will take? If you offer your data to a bank or other financial institution, they will probably have a stated policy on how many decimal points they will retain. Most banks in the USA will draw the line at mils - i.e. thousandths of a dollar. Some might go as far as tenth of a mil. If you retain more digits than they do, you will almost surely come up with different answers than they would. And remember that if there is a discrepancy in a computation, the bank is arrogant enough to say that they are right and you are wrong. Big business gets like that.

It is commonplace for a requirement to "draw the line" at how many digits a business will keep. See, for example, Access data type "Currency" - which is a scaled integer that draws the line at 0.1 millidollars. There is a quote from one of the Civilization offshoots - "Civilization: Beyond Earth" - where the game makes the snide remark about people computing things to many degrees of vacuous precision.

The question Dave (CheekyBuddha) asked earlier is relevant. Are you plotting inter-planetary flight paths? If you are not, you are probably making incorrect assumptions over what you really needed.

If this is a monetary computation, you have to ask at what point your "partners in crime" will accept numbers. You would do better to limit your work to their standards. Is it a manufacturing process with precise tolerances? Vernier calipers or servo-motors have limited physical precision. You could measure the size of a physically holdable object in units of angstroms (= 0.1 nanometers, 10 digits, and the estimated size of simple molecules) and still have more digits than you need. Are you cutting something to a precise amount? The limit of precision is the width of the cutting device's narrowest cut. Don't expect micrometer tolerances in the hull of a battleship. Are we talking industrial chemistry? The scales used to measure bulk components to be mixed rarely need to go farther than grams, though milligrams are easy and micrograms are possible. But micrograms is only six decimal places. The real world isn't usually that precise.

The most precise clock on Earth is the Cesium 137 (so-called) "fountain" clocks, which actually DO have 16 digits of precision. That level of precision, however, means they are accurate to 1 second per 100-300 MILLION years (apparently, depending on specific brands of such clocks). And they all work using integers, by the way. An atomic clock works by counting atomic oscillation - yes, INTEGER counting - for things that vibrate at the atomic level at rates of 10,000,000,000,000,000 times per second, or frequencies of 10 quadrillion Hz. Though there is talk of a newer design that might make it to 10 QUINtillion Hz. In the prefixes, 10 QUADrillion is 10 PetaHz and 10 QUINtillion is 10 ExaHz. (I'll be honest, I had to look up that prefix because I don't usually work at the Peta/Exa level.)

Thanks, I need to pick limits to work with & they're going through several multipliers, being converted to percentages & multiplied against each other. So compounding has a massive effect very quickly after the decimal place

But you are looking at the wrong end of the telescope. The farther to the left you get, the bigger the significance of the digits you keep. Once you get past a certain point to the right, the digits become insignificant in every sense of the word. Even if you are dealing with devalued currency, the worst currency in the world today is the Lebanese Pound, which trades at about 123,000 LBP (rounded) to the dollar. OK, so you need six extra digits when computing exchange rates for that. But 16 digits? Let's say that the base unit has fractional currency for four places. Their exchange rate is over 100,000 to 1 with dollars. That's another six places. What are you going to do with the remaining 6 fractional digits?

In summary, my advice is (a) to recognize that if you want X digits of precision, you have to START with X digits of precision including any numeric constants involved in scaling or interest computations, and (b) past a certain point, I GUARANTEE you that unless you are working with Cesium atomic clocks or devices of extraterrestrial origin, there will be physical or policy limits on how many digits of precision you CAN use. And my point (b) is actually the point you should examine first as it colors what you have to do with point (a) at the start of your end-goal project.

Consider this as a commentary on the philosophy of precision.
 
Thanks Doc & all.
The question Dave (CheekyBuddha) asked earlier is relevant. Are you plotting inter-planetary flight paths? If you are not, you are probably making incorrect assumptions over what you really needed.
Oh I don't doubt it & I hope I haven't been rude to you @cheekybuddha, especially after so much help you've given me, if so I do apologize. I believe with my current design the precision & scales were required at that sort of magnitude. However I'm starting to realize going through many different multipliers with multiplication/ division is not possible as p & s is going to increase massively & rendered impossible after just one or two multipliers so I need to see if I can find an alternative method/ logic :eek:.Many different multipliers would have been the ideal scenario but I'm seeing if there is an alternative.
Now I have a bit more knowledge on how to calculate precision requirements I stand half a chance I think :geek:.
Massive, massive thanks again.
 
Last edited:
Overall this has been a veyr interesting discussion that I learned from, thanks everyone who contributed including the OP who asked the valuable question. I'm less than an expert in these ultra high-precision scenarios, although I've read enough articles to believe the sql's Currency is junk and should be avoided if you care about precision so I avoid it and generally always opt for decimal(19,6) for most cases where a decimal of any kind will be needed which has served me pretty well. My opinion on currency was formed after reading a lot of articles by the pro's out there on sql server but isn't something where I remember the particular's enough to be able to defend it articulate now.

My companny other than me is in the habit of throwing Floats everywhere, which I guess works well enough for our precision needs.
 

Users who are viewing this thread

Back
Top Bottom