March 8, 2015 at 10:22 pm
Jeff Moden (3/8/2015)
Not sure why it didn't work out that way on my box. Heh... and no... it wasn't due to limited column width. 😛
Probably it has something to do with the fact that the original post was updated since then.
But the current version also uses FLOAT data type all over the place, so whatever was the problem back then it was not the use of FLOAT data type.
_____________
Code for TallyGenerator
August 28, 2016 at 3:16 pm
Is great, thanks
August 29, 2016 at 6:03 am
vdiazh (8/28/2016)
Is great, thanks
I hope you're not using the FLOAT version. FLOAT is a binary "approximation" of numbers and it the scale varies according to how many numbers are used to the left of the decimal point, which is a disadvantage in this case.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2016 at 7:26 am
Jeff Moden (8/29/2016)
vdiazh (8/28/2016)
Is great, thanksI hope you're not using the FLOAT version. FLOAT is a binary "approximation" of numbers and it the scale varies according to how many numbers are used to the left of the decimal point, which is a disadvantage in this case.
There is no better data type for "rate" values than float.
Any kind of decimal will be less precise than float.
_____________
Code for TallyGenerator
August 29, 2016 at 7:43 am
Sergiy (8/29/2016)
Jeff Moden (8/29/2016)
vdiazh (8/28/2016)
Is great, thanksI hope you're not using the FLOAT version. FLOAT is a binary "approximation" of numbers and it the scale varies according to how many numbers are used to the left of the decimal point, which is a disadvantage in this case.
There is no better data type for "rate" values than float.
Any kind of decimal will be less precise than float.
Someone would have to prove that one to me, Sergiy. I DO understand the benefits of FLOAT for certain things but don't believe that things like amortization tables fit the bill here. Of course, that's based on a previous bad experience and I may have done something incorrectly. Unfortunately, I no loger have the code that cause the "penny skew".
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2016 at 10:20 am
Yes, i am use the float version, there are another version?, I will be to read all post.
August 29, 2016 at 4:54 pm
Jeff Moden (8/29/2016)
Someone would have to prove that one to me, Sergiy.
It took 2 minutes:
DECLARE @Amount MONEY, @Term INT
DECLARE @fRate float, @dRate decimal (38,10)
SELECT @Amount = 1000000, @Term = 17*9*13*7
SELECT @fRate = CONVERT(FLOAT, @Amount)/@Term,
@dRate = CONVERT(DECIMAL(38,10), @Amount)/@Term
SELECT @fRate, @fRate * @Term fAmount,
@dRate, @dRate * @Term dAmount
_____________
Code for TallyGenerator
August 29, 2016 at 6:05 pm
To make it really painful:
DECLARE @Amount MONEY, @Term INT
DECLARE @fRate float, @dRate decimal (38,28)
SELECT @Amount = 1000000, @Term = 17*3*13*7*19*23*43*11
SELECT @fRate = CONVERT(FLOAT, @Amount)/@Term,
@dRate = CONVERT(DECIMAL(38,28), @Amount)/@Term
SELECT @fRate [@fRate], @fRate * @Term / @fRate /@Term * @fRate * @Term fAmount,
@dRate [@dRate], @dRate * @Term / @dRate /@Term * @dRate * @Term dAmount
@fRatefAmount@dRatedAmount
0.0010424274856246410000000.0010424274856246356996090128999589.913322
What's the point in all those extra "precise" digits in decimal rate if just after 5 simple arithmetic operations you're $410 off the correct result?
Turns out, all those 12 extra digits (taking extra 5 bytes per each value of this type) are fake, non-truthful.
Just a waste of server resources with negative outcome.
_____________
Code for TallyGenerator
August 29, 2016 at 9:18 pm
Sergiy (8/29/2016)
Jeff Moden (8/29/2016)
Someone would have to prove that one to me, Sergiy.It took 2 minutes:
DECLARE @Amount MONEY, @Term INTDECLARE @fRate float, @dRate decimal (38,10)
SELECT @Amount = 1000000, @Term = 17*9*13*7
SELECT @fRate = CONVERT(FLOAT, @Amount)/@Term,
@dRate = CONVERT(DECIMAL(38,10), @Amount)/@Term
SELECT @fRate, @fRate * @Term fAmount,
@dRate, @dRate * @Term dAmount
Took just a glance to realize that you crippled the decimal scale to be less than that of the FLOAT return and only slightly more to fix it. 😉
DECLARE @Amount MONEY, @Term INT
DECLARE @fRate float, @dRate decimal (38,13)
SELECT @Amount = 1000000, @Term = 17*9*13*7
SELECT @TERM
SELECT @fRate = CONVERT(FLOAT, @Amount)/@Term,
@dRate = CONVERT(DECIMAL(38,13), @Amount)/@Term
SELECT @fRate, @fRate * @Term fAmount,
@dRate, @dRate * @Term dAmount
Normally, though, I like the code to be at least as resolute as Granny's dollar store 4 function calculator and will use a scale of at least 15.
Checking your second example. Good list of prime numbers. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 29, 2016 at 9:55 pm
Jeff Moden (8/29/2016)
Normally, though, I like the code to be at least as resolute as Granny's dollar store 4 function calculator and will use a scale of at least 15.
Here are 28 digits.
Must be more than on Granny's calculator:
DECLARE @Amount MONEY, @Term INT
DECLARE @fRate float, @dRate decimal (38,28)
DECLARE @i INT, @fOutput FLOAT, @dOutput DECIMAL (38,28)
SELECT @Amount = 1000000, @Term = 12*17
SELECT@fRate = CONVERT(FLOAT, @Amount)/@Term,
@dRate = CONVERT(DECIMAL(38,28), @Amount)/@Term
SELECT@fOutput = @fRate * @Term,
@dOutput = @dRate * @Term
SET @I = 5
WHILE @i > 0
BEGIN
SET @fOutput = @fOutput / @fRate /@Term * @fRate * @Term
SET @dOutput = @dOutput / @dRate /@Term * @dRate * @Term
SET @I = @I - 1
END
SELECT@fOutput [@fOutput] , SQL_VARIANT_PROPERTY(@fOutput, 'MaxLength'), @dOutput [@dOutput], SQL_VARIANT_PROPERTY(@dOutput, 'MaxLength')
Good list of prime numbers. 😀
I must have tried to make FLOAT to show any weakness.
Did not happen.
In the last example I made the list shorter, to make the case more "real-life".
Did not help much.
DECIMAL is still proven to be too imprecise.
_____________
Code for TallyGenerator
August 29, 2016 at 11:36 pm
Heh... let's hope the programmers in the IRS aren't using FLOAT to make decisions.
DECLARE @Amount MONEY, @Term INT
DECLARE @fRate float, @dRate decimal (38,15)
SELECT @Amount = 1000000, @Term = 17*3*13*7*19*23*43*11
SELECT @Term
SELECT @fRate = CONVERT(FLOAT, @Amount)/@Term,
@dRate = CONVERT(DECIMAL(38,15), @Amount)/@Term
SELECT @fRate [@fRate], @fRate * @Term / @fRate /@Term * @fRate * @Term fAmount,
@dRate [@dRate], @dRate * @Term / @dRate /@Term * @dRate * @Term dAmount
SELECT CASE
WHEN @fRate * @Term / @fRate /@Term * @fRate * @Term >= 1000000 THEN 'Paid in Full'
ELSE 'Go to Jail for not paid in full.'
END
, CAST(@fRate * @Term / @fRate /@Term * @fRate * @Term AS DECIMAL(38,28)) TrueFloatvalueIsWhyCaseFailed
FLOAT still comes up with the wrong answer but because of the rounding MS does to keep folks from flipping their wigs, lot's of folks are happy with FLOAT. The problem is that MS didn't apply such rounding consistently and so you end up with problems when it comes to making decisions based on those numbers. Hopefully, folks using FLOAT are aware of where those inconsistencies are and will round to the nearest penny to get the correct answer. BWAHAA!!! Except maybe for "Bankers Rounding". :hehe:
The thing that sucks even worse is that if over 38 digits of precision are reached for any reason, MS decided to role back to just 6 or fewer digits of scale for the DECIMAL data type. So you have to do like Granny's calculator and resize the number after every calculation that occurs when she presses a mathematical operator button. Not fun.
BTW... that's a hell of a TERM for a loan. Even if the PERIOD is an hour for that TERM, it's still almost 110,000 years. I hope you live that long and I hope the last voice you hear is mine. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
August 30, 2016 at 12:58 am
Jeff Moden (8/29/2016)
Heh... let's hope the programmers in the IRS aren't using FLOAT to make decisions.
You better hope they do.
Why - see below.
DECLARE @Amount MONEY, @Term INT
DECLARE @fRate float, @dRate decimal (38,15)
SELECT @Amount = 1000000, @Term = 17*3*13*7*19*23*43*11
SELECT @Term
SELECT @fRate = CONVERT(FLOAT, @Amount)/@Term,
@dRate = CONVERT(DECIMAL(38,15), @Amount)/@Term
SELECT @fRate [@fRate], @fRate * @Term / @fRate /@Term * @fRate * @Term fAmount,
@dRate [@dRate], @dRate * @Term / @dRate /@Term * @dRate * @Term dAmount
SELECT CASE
WHEN @fRate * @Term / @fRate /@Term * @fRate * @Term >= 1000000 THEN 'Paid in Full'
ELSE 'Go to Jail for not paid in full.'
END
, CAST(@fRate * @Term / @fRate /@Term * @fRate * @Term AS DECIMAL(38,28)) TrueFloatvalueIsWhyCaseFailed
FLOAT still comes up with the wrong answer but because of the rounding MS does to keep folks from flipping their wigs, lot's of folks are happy with FLOAT.
I can tell you a secret - folks from IRS do roundings too.
Rounding to the nearest cent.
In fact - everybody who operates with monetary values does.
Because there are no money units less than 1 cent.
Therefore, when you compare amounts you must make sure both compared values are presented in correct data type which would be appropriate for monetary amounts - MONEY in case of SQL Server.
So, it's not FLOAT comes up with a wrong answer, it's you come up with a wrong formula.
Correct one wold be :
SELECT CASE
WHEN CONVERT(MONEY, @fRate * @Term / @fRate /@Term * @fRate * @Term) >= 1000000 THEN 'Paid in Full'
ELSE 'Go to Jail for not paid in full.'
END
, CAST(@fRate * @Term / @fRate /@Term * @fRate * @Term AS DECIMAL(38,28)) TrueFloatvalueIsWhyCaseFailed
BTW, the "TrueFloatvalueIsWhyCaseFailed" you display here is not really "True Float value", it's DECIMAL representation of true FLOAT value.
it's not quite the same.
Here is the proof:
DECLARE @FloatValue float, @DecimalValue DECIMAL(38,28)
SELECT @FloatValue = CONVERT(FLOAT, 1) / 3
SELECT @DecimalValue = @FloatValue
SELECT@FloatValue, CONVERT(DECIMAL(38,28), @FloatValue * 3),
@DecimalValue, @DecimalValue * 3
The thing that sucks even worse is that if over 38 digits of precision are reached for any reason, MS decided to role back to just 6 or fewer digits of scale for the DECIMAL data type. So you have to do like Granny's calculator and resize the number after every calculation that occurs when she presses a mathematical operator button. Not fun.
MS do no role to REAL precision, they keep it as precise as possible.
And I must say - DECIMAL calculations use twice as deep computation precision comparing to FLOAT.
It's math of fixed point calculations which sucks.
Fixed decimal point - that's what kills precision.
When you divide DECIMAL (38,28) by 1000 you get a number of DECIMAL (35, 25).
If you multiply it again by the same 1000 you cannot get your 3 lost precise digits back.
So, your real precision will be DECIMAL(38,25).
Any further operation on this number will steal more truthful digits from it:
DECLARE @Amount MONEY, @Term INT
DECLARE @fRate float, @dRate decimal (38,28)
DECLARE @i INT, @fOutput FLOAT, @dOutput DECIMAL (38,28)
SELECT @Amount = 1000000, @Term = 12*17
SELECT@fRate = CONVERT(FLOAT, @Amount)/@Term,
@dRate = CONVERT(DECIMAL(38,28), @Amount)/@Term
SELECT@fOutput = @fRate * @Term,
@dOutput = @dRate * @Term
SET @I = 5
WHILE @i > 0
BEGIN
SET @fOutput = @fOutput / @fRate /@Term * @fRate * @Term
SET @dOutput = @dOutput / @dRate /@Term * @dRate * @Term
SELECT @fOutput, @dOutput
SET @I = @I - 1
END
As you can see, the precision of decimals decreases with every iteration. And you cannot do anything about it.
To keep the precision on the same level you need to use DECIMAL(76, 56) for the result of multiplication/division operations.
BTW, I never said FLOAT is perfect in terms of precision.
FLOAT uses a limited number of bytes, therefore it has a limited presision, of course.
I said that FLOAT is the best of them all.
It's way more precise than any of DECIMAL data types.
In your example I calculated for how much money you wanted me to go to prison:
, @fRate * @Term / @fRate /@Term * @fRate * @Term - 1000000 [Not paid amount],
It returned -1.16415321826935E-10.
Well, a judge would be impressed.
Not sure how he/she would want me to repay this amount though.
Let's see what will "precise" decimals give us:
SELECT CASE
WHEN CONVERT(MONEY, @dRate * @Term / @dRate /@Term * @dRate * @Term) >= 1000000 THEN 'Paid in Full'
ELSE 'Go to Jail for not paid in full.'
END,
@dRate * @Term / @dRate /@Term * @dRate * @Term - 1000000 [Not paid amount]
Output:
----------------------------------------------
Go to Jail for not paid in full.-410.086678
A bit more than one hundredth of a millionth of a cent.
Here collectors would have a reason for a quick roundtrip to my house.
Don't you think?
I guess the conclusion is obvious :
Never ever use DECIMAL data types for loan calculations.
FLOAT and only FLOAT.
But do not forget to convert the final number to MONEY.
BTW... that's a hell of a TERM for a loan. Even if the PERIOD is an hour for that TERM, it's still almost 110,000 years. I hope you live that long and I hope the last voice you hear is mine. 😛
I used 17 years in my last example. DECIMAL still is proven not good enough to be used for loan calculations.
_____________
Code for TallyGenerator
August 30, 2016 at 1:05 am
Sergiy (8/29/2016)
To make it really painful:
DECLARE @Amount MONEY, @Term INTDECLARE @fRate float, @dRate decimal (38,28)
SELECT @Amount = 1000000, @Term = 17*3*13*7*19*23*43*11
SELECT @fRate = CONVERT(FLOAT, @Amount)/@Term,
@dRate = CONVERT(DECIMAL(38,28), @Amount)/@Term
SELECT @fRate [@fRate], @fRate * @Term / @fRate /@Term * @fRate * @Term fAmount,
@dRate [@dRate], @dRate * @Term / @dRate /@Term * @dRate * @Term dAmount
@fRatefAmount@dRatedAmount0.0010424274856246410000000.0010424274856246356996090128999589.913322
It strikes me as odd to hear that the FLOAT / REAL datatype is "precise" when by definition it is "imprecise". The following quote is taken from the MSDN page for float and real:
Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.
What's the point in all those extra "precise" digits in decimal rate if just after 5 simple arithmetic operations you're $410 off the correct result?
Well, I would say that your test is misleading as nobody does (or hopefully doesn't do) financial calculations in that manner. What you are calling "rate" is really the payment amount, which is a currency, which needs to be rounded to 2 decimal places. So if that is $410 off, then it is due to bad code, not a bad datatype.
Turns out, all those 12 extra digits (taking extra 5 bytes per each value of this type) are fake, non-truthful. Just a waste of server resources with negative outcome.
Wrong.
1) those 12 "extra" digits should have never been there in the first place for a financial calculation.
2) They aren't fake or wrong. They are more accurate than you think, and what you think you are seeing as the "correct" number isn't what you think it is. Take a closer look and you might see something "fishy" going on. In order to help see this, I modified one of your examples to show that the "float" value multiplied by the "term" isn't the original "amount" that you appear to be getting. What you are seeing from the "float" operation is the wacky rounding that Jeff mentioned, and this is not consistent between languages and applications.
DECLARE @Amount MONEY, @Term INT
DECLARE @fRate float, @dRate decimal (38,28)
SELECT @Amount = 100000, @Term = 12*17
SELECT@fRate = CONVERT(FLOAT, @Amount)/@Term,
@dRate = CONVERT(DECIMAL(38,28), @Amount)/@Term
SELECT@fRate AS [fRate], @dRate AS [dRate], @Term AS [Term];
-- 490.196078431373 490.1960784313725490196078431372 204
SELECTCONVERT(DECIMAL(38,0), @fRate * 1000000000000) AS [DecimalFromFloat],
CONVERT(BIGINT, CONVERT(DECIMAL(38,0), @fRate * 1000000000000)) AS [BigIntFromDecimalFromFloat],
CONVERT(BIGINT, CONVERT(DECIMAL(38,0), @fRate * 1000000000000)) * @Term;
-- 490196078431373 490196078431373 100000000000000092
SELECTCONVERT(DECIMAL(38,0), @fRate * 1000000000000000) AS [BiggerDecimalFromFloat];
-- 490196078431372540
A very quick sanity-check of the multiplication operation would be to take the ending digits of the "term" -- 4 from 204 -- and the "fRate" -- 3 from 490.196078431373 -- and multiply them. 4 * 3 = 12, which ends with a "2", yet your test showed that the "@fOutput" value was an even "1000000", which ends in a "0". How can that be? The results shown above for the 2nd SELECT show this by turning the FLOAT into a BIGINT (so that there can be no argument over rounding, etc).
The 3rd SELECT shows that the FLOAT is internally represented by more digits than are being displayed when left as a FLOAT.
FLOAT is more noticeably "imprecise" in .NET where sometimes an extra 0.000000000000000000005 is added a value. FLOAT is used when you need more than 38 digits for a number (since that is the max for DECIMAL). And when this is needed, then you give up the accuracy of DECIMAL, but at that point, it is worth the trade-off. But for financial calculations, FLOAT can get you into trouble.
P.S. MONEY is really a DECIMAL(19, 4). There is a good reason why MS didn't use FLOAT as the underlying structure for MONEY / SMALLMONEY.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
August 30, 2016 at 1:57 am
Solomon Rutzky (8/30/2016)
. In order to help see this, I modified one of your examples to show that the "float" value multiplied by the "term" isn't the original "amount" that you appear to be getting. What you are seeing from the "float" operation is the wacky rounding that Jeff mentioned, and this is not consistent between languages and applications.
DECLARE @Amount MONEY, @Term INTDECLARE @fRate float, @dRate decimal (38,28)
SELECT @Amount = 100000, @Term = 12*17
SELECT@fRate = CONVERT(FLOAT, @Amount)/@Term,
@dRate = CONVERT(DECIMAL(38,28), @Amount)/@Term
SELECT@fRate AS [fRate], @dRate AS [dRate], @Term AS [Term];
-- 490.196078431373 490.1960784313725490196078431372 204
SELECTCONVERT(DECIMAL(38,0), @fRate * 1000000000000) AS [DecimalFromFloat],
CONVERT(BIGINT, CONVERT(DECIMAL(38,0), @fRate * 1000000000000)) AS [BigIntFromDecimalFromFloat],
CONVERT(BIGINT, CONVERT(DECIMAL(38,0), @fRate * 1000000000000)) * @Term;
-- 490196078431373 490196078431373 100000000000000092
SELECTCONVERT(DECIMAL(38,0), @fRate * 1000000000000000) AS [BiggerDecimalFromFloat];
-- 490196078431372540
🙂
What am I supposed to see from this?
Only point you managed to prove is that DECIMAL calculations are imprecise:
CONVERT(BIGINT, CONVERT(DECIMAL(38,0), @fRate * 1000000000000)) * @Term;
You convert to DECIMAL before multiplying, it's not a FLOAT calculation anymore, but DECIMAL.
Make it FLOAT:
CONVERT(BIGINT, CONVERT(DECIMAL(38,0), @fRate * 1000000000000 * @Term));
and all the defects of DECIMAL calculations will be gone.
A very quick sanity-check of the multiplication operation would be to take the ending digits of the "term" -- 4 from 204 -- and the "fRate" -- 3 from 490.196078431373 -- and multiply them. 4 * 3 = 12, which ends with a "2", yet your test showed that the "@fOutput" value was an even "1000000", which ends in a "0". How can that be?
You're making the same mistake - you take DECIMAL representation of FLOAT you see on the screen for the actual FLOAT value stored in the memory. It's not
DECLARE @FloatValue float, @DecimalValue DECIMAL(38,28)
SELECT @FloatValue = CONVERT(FLOAT, 1) / 3
SELECT @DecimalValue = @FloatValue
SELECT CONVERT(BIGINT, @FloatValue *3 * 1000000000000) - CONVERT(BIGINT, @DecimalValue *3 * 1000000000000)
The results shown above for the 2nd SELECT show this by turning the FLOAT into a BIGINT (so that there can be no argument over rounding, etc).
That SELECT never turned FLOAT to BIGINT.
It turned FLOAT to DECIMAL(38,28), and then turned DECIMAL(38,28) to FLOAT.
Naturally, a wrong assumption leads to a wrong conclusion.
P.S. MONEY is really a DECIMAL(19, 4). There is a good reason why MS didn't use FLOAT as the underlying structure for MONEY / SMALLMONEY.
MONEY, in real life, is DECIMAL(NN, 2).
There is no such money as "0.1 cent" in real life.
2 extra digits are added because mathematically illiterate people use to calculate and store rates as MONEY, and then use them in monetary computations.
Not to punish them for this innocent sin "standard people" added 2 extra digits to MONEY data type, so the result of such calculations may remain within 1 cent precision, at least for simplest cases.
_____________
Code for TallyGenerator
August 30, 2016 at 2:11 am
Solomon Rutzky (8/30/2016)
It strikes me as odd to hear that the FLOAT / REAL datatype is "precise" when by definition it is "imprecise". The following quote is taken from the MSDN page for float and real:Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.
It's not the only rubbish you can find on MSDN.
What's the point in all those extra "precise" digits in decimal rate if just after 5 simple arithmetic operations you're $410 off the correct result?
I see you agreed that DECIMAL "precision" is fake.
Well, I would say that your test is misleading as nobody does (or hopefully doesn't do) financial calculations in that manner.
What manner would you do it?
What you are calling "rate" is really the payment amount, which is a currency, which needs to be rounded to 2 decimal places.
No, it's definitely a rate.
You'll get the amount when you multiply the rate by the number of terms the payment suppose to cover.
So if that is $410 off, then it is due to bad code, not a bad datatype.
Can you illustrate this?
The code appears to be quite good when FLOAT data type is used, but fails miserably in case of DECIMAL.
Can you prove otherwise?
_____________
Code for TallyGenerator
Viewing 15 posts - 46 through 60 (of 97 total)
You must be logged in to reply to this topic. Login to reply