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