Float Data Type for Money

Hi,
I'm now supporting a production database that uses the float data type to
store monetary values in one of the tables. No this is not my design but I
am required to support it and to generate reports for the data. I
understand that the float/real data types round incorrectly. How can I
round the data correctly? Here is an sample float value:
1139.3099999999999
I need 1139.31.
Thanks
JerryI also need to have any trailing zeros removed as well.
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OCxn4jjvFHA.908@.tk2msftngp13.phx.gbl...
> Hi,
> I'm now supporting a production database that uses the float data type to
> store monetary values in one of the tables. No this is not my design but
> I am required to support it and to generate reports for the data. I
> understand that the float/real data types round incorrectly. How can I
> round the data correctly? Here is an sample float value:
> 1139.3099999999999
> I need 1139.31.
> Thanks
> Jerry
>|||This seemed to work:
convert(decimal(10,2),round(convert(mone
y,column),2)) --column is the
float column
Is this ok or...?
Thanks
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:emIDnmjvFHA.3400@.TK2MSFTNGP14.phx.gbl...
>I also need to have any trailing zeros removed as well.
> Thanks
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OCxn4jjvFHA.908@.tk2msftngp13.phx.gbl...
>|||You cannot support it. It will not work, thanks to floating point
rounding errors. It is also illegal in the EU and in violation of GAAP
in the United States. Under SOX, there is a good chance that your boss
is going to jail for this kind of accounting. I would update my resume
and send the boss a letter so that you do not get caught up in the mess
that is coming.
Oh, the stinking, dirty, unusable kludge is CAST() and/or ROUND(). Do
not convert to MONEY -- it is proprietary and has funny math.|||--CELKO-- wrote:
> You cannot support it. It will not work, thanks to floating point
> rounding errors. It is also illegal in the EU and in violation of GAAP
> in the United States. Under SOX, there is a good chance that your boss
> is going to jail for this kind of accounting. I would update my resume
> and send the boss a letter so that you do not get caught up in the mess
> that is coming.
> Oh, the stinking, dirty, unusable kludge is CAST() and/or ROUND(). Do
> not convert to MONEY -- it is proprietary and has funny math.
Hi Joe,
Please, please, please, can you post a reference to the illegality of
this in the EU? I can find *nothing* online (other than Euro
*conversion* rules), and We're about to have a new system introduced
here that uses floating point all over the place for currency, so if
you could provide a reference, I might be able to force a change to the
system.
Damien|||Thanks Joe.
Scary thing is it's an accounting-based software package. Yeah I noticed
this and about 5 other "Why did you...?" yesterday afternoon when I was
meeting with the vendor. I'll follow up with the vendor, management and
accounting today. Do you have any links that support the violation that I
can forward on?
For the reports I'll be generating in RS for account aging, what would the
kludge code look like to round to 2 decimal places and trucate trailing
zeros? Here is the code I came up with:
convert(decimal(10,2),round(convert(mone
y,column),2))
Thanks
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127274857.269620.320950@.g43g2000cwa.googlegroups.com...
> You cannot support it. It will not work, thanks to floating point
> rounding errors. It is also illegal in the EU and in violation of GAAP
> in the United States. Under SOX, there is a good chance that your boss
> is going to jail for this kind of accounting. I would update my resume
> and send the boss a letter so that you do not get caught up in the mess
> that is coming.
> Oh, the stinking, dirty, unusable kludge is CAST() and/or ROUND(). Do
> not convert to MONEY -- it is proprietary and has funny math.
>|||On Wed, 21 Sep 2005 08:46:44 -0700, Jerry Spivey wrote:
(snip)
>For the reports I'll be generating in RS for account aging, what would the
>kludge code look like to round to 2 decimal places and trucate trailing
>zeros? Here is the code I came up with:
> convert(decimal(10,2),round(convert(mone
y,column),2))
Hi Jerry,
No need to make it that complicated.
SELECT CONVERT(decimal(10,2), BadlyTypedColumn)
will do.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Overcomplicating things again...damn! ;-)
Thanks Hugo
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:p8i3j1p1g9bcicg3doqjdpca4qocv534dp@.
4ax.com...
> On Wed, 21 Sep 2005 08:46:44 -0700, Jerry Spivey wrote:
> (snip)
> Hi Jerry,
> No need to make it that complicated.
> SELECT CONVERT(decimal(10,2), BadlyTypedColumn)
> will do.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is a quick "cut & paste":
The MONEY datatype has rounding errors. Using more than one operation
(multiplication or division) on money columns will produce severe
rounding errors. A simple way to visualize money arithmetic is to place
a ROUND() function calls after every operation. For example,
Amount = (Portion / total_amt) * gross_amt
can be rewritten using money arithmetic as:
Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)
Rounding to four decimal places might not seem an issue, until the
numbers you are using are greater than 10,000.
BEGIN
DECLARE @.gross_amt MONEY,
@.total_amt MONEY,
@.my_part MONEY,
@.money_result MONEY,
@.float_result FLOAT,
@.all_floats FLOAT;
SET @.gross_amt = 55294.72;
SET @.total_amt = 7328.75;
SET @.my_part = 1793.33;
SET @.money_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.float_result = (@.my_part / @.total_amt) * @.gross_amt;
SET @.Retult3 = (CAST(@.my_part AS FLOAT)
/ CAST( @.total_amt AS FLOAT))
* CAST(FLOAT, @.gross_amtAS FLOAT);
SELECT @.money_result, @.float_result, @.all_floats;
END;
@.money_result = 13525.09 -- incorrect
@.float_result = 13525.0885 -- incorrect
@.all_floats = 13530.5038673171 -- correct, with a -5.42 error|||Thanks Joe!
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127417068.157889.200980@.g14g2000cwa.googlegroups.com...
> Here is a quick "cut & paste":
> The MONEY datatype has rounding errors. Using more than one operation
> (multiplication or division) on money columns will produce severe
> rounding errors. A simple way to visualize money arithmetic is to place
> a ROUND() function calls after every operation. For example,
> Amount = (Portion / total_amt) * gross_amt
> can be rewritten using money arithmetic as:
> Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)
> Rounding to four decimal places might not seem an issue, until the
> numbers you are using are greater than 10,000.
> BEGIN
> DECLARE @.gross_amt MONEY,
> @.total_amt MONEY,
> @.my_part MONEY,
> @.money_result MONEY,
> @.float_result FLOAT,
> @.all_floats FLOAT;
> SET @.gross_amt = 55294.72;
> SET @.total_amt = 7328.75;
> SET @.my_part = 1793.33;
> SET @.money_result = (@.my_part / @.total_amt) * @.gross_amt;
> SET @.float_result = (@.my_part / @.total_amt) * @.gross_amt;
> SET @.Retult3 = (CAST(@.my_part AS FLOAT)
> / CAST( @.total_amt AS FLOAT))
> * CAST(FLOAT, @.gross_amtAS FLOAT);
> SELECT @.money_result, @.float_result, @.all_floats;
> END;
> @.money_result = 13525.09 -- incorrect
> @.float_result = 13525.0885 -- incorrect
> @.all_floats = 13530.5038673171 -- correct, with a -5.42 error
>