RE: Money datatype in SQL Server
Well... I use the money datatype in SQL server for our prices. I have had not issues. When we output, we use #dollarformat(queryname.price)#. Works for what we do. -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2008 9:23 AM To: CF-Talk Subject: Money datatype in SQL Server Is there any bottom line recommendation on using the Money datatype in SQL Server? I've googled and found a lot of folks say not to use it because of accuracy issues (and of course, the fact that it's proprietary), but I haven't seen any that suggest a better alternative. My understanding is that Money is exactly the same as Decimal(19,4)... so if there are accuracy issues with Money, wouldn't those same issues exist with a Decimal datatype? I saw one link that suggested that Floats would be the most accurate... but I thought floats were inherently *not* accurate. Can anybody shed a bit of light? Thanks! Charlie -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307180 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Money datatype in SQL Server
hmm... this was the page i saw that seemed to strongly suggest that the money datatype could be problematic in terms of accuracy (even provided sample SQL to run in query analyzer to see the results): http://tinyurl.com/6fh8hl On Tue, Jun 10, 2008 at 9:55 AM, Jacob [EMAIL PROTECTED] wrote: Well... I use the money datatype in SQL server for our prices. I have had not issues. When we output, we use #dollarformat(queryname.price)#. Works for what we do. -Original Message- From: Charlie Griefer [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2008 9:23 AM To: CF-Talk Subject: Money datatype in SQL Server Is there any bottom line recommendation on using the Money datatype in SQL Server? I've googled and found a lot of folks say not to use it because of accuracy issues (and of course, the fact that it's proprietary), but I haven't seen any that suggest a better alternative. My understanding is that Money is exactly the same as Decimal(19,4)... so if there are accuracy issues with Money, wouldn't those same issues exist with a Decimal datatype? I saw one link that suggested that Floats would be the most accurate... but I thought floats were inherently *not* accurate. Can anybody shed a bit of light? Thanks! Charlie -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307181 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Money datatype in SQL Server
Charlie Griefer wrote: Is there any bottom line recommendation on using the Money datatype in SQL Server? I've googled and found a lot of folks say not to use it because of accuracy issues (and of course, the fact that it's proprietary), but I haven't seen any that suggest a better alternative. My understanding is that Money is exactly the same as Decimal(19,4)... If that is the case, why would you want to use it instead of Decimal(19,4)? What value does it add to your application? Jochem ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307187 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Money datatype in SQL Server
On Tue, Jun 10, 2008 at 10:34 AM, Jochem van Dieten [EMAIL PROTECTED] wrote: Charlie Griefer wrote: Is there any bottom line recommendation on using the Money datatype in SQL Server? I've googled and found a lot of folks say not to use it because of accuracy issues (and of course, the fact that it's proprietary), but I haven't seen any that suggest a better alternative. My understanding is that Money is exactly the same as Decimal(19,4)... If that is the case, why would you want to use it instead of Decimal(19,4)? What value does it add to your application? There are some people here who suggest that it makes the database more self documenting, and I don't disagree with that... but if there are indeed accuracy issues I think that outweighs the self-documenting aspect :) What I'm not fully understanding is if those same accuracy issues exist with Decimal(19,4). And if they do, is the solution to just be judicious in the use of Round() functions? or is using Float the preferred option. Thanks, Charlie -- A byte walks into a bar and orders a pint. Bartender asks him What's wrong? Byte says Parity error. Bartender nods and says Yeah, I thought you looked a bit off. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307189 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Money datatype in SQL Server
Make sure you read this message as UTF-8 (the list still strips the
charset from the content-type header).
Charlie Griefer wrote:
There are some people here who suggest that it makes the database more
self documenting, and I don't disagree with that... but if there are
indeed accuracy issues I think that outweighs the self-documenting
aspect :)
Would you consider this an accuracy issue?
create table test (mtype money);
insert into test (mtype) values (' 3');
insert into test (mtype) values ('$ 3');
select * from test;
I do :)
What I'm not fully understanding is if those same accuracy issues
exist with Decimal(19,4).
Just like money decimal is an exact datatype, i.e. no subtle rounding
errors at the 8+ digit are introduced. That does not mean there are no
algorithmic rounding errors. I demonstrated a very clear one where
stripping the currency symbol gave 3 euro and 3 dollar the same
values. There are other possible algorithmic errors such as rounding
between operations instead of only at the end. (Or not rounding between
operations if that is what is required for your currency.)
And if they do, is the solution to just be
judicious in the use of Round() functions? or is using Float the
preferred option.
float is never the preferred solution when accuracy matters because it
adds the rounding problems to all the algorithmic problems.
Jochem
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307191
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Money datatype in SQL Server
Retry, and hopefully the charset stuff now works.
Make sure you read this message as UTF-8 (the list still strips the
charset from the content-type header).
Charlie Griefer wrote:
There are some people here who suggest that it makes the database more
self documenting, and I don't disagree with that... but if there are
indeed accuracy issues I think that outweighs the self-documenting
aspect :)
Would you consider this an accuracy issue?
create table test (mtype money);
insert into test (mtype) values ('⬠3');
insert into test (mtype) values ('$ 3');
select * from test;
I do :)
What I'm not fully understanding is if those same accuracy issues
exist with Decimal(19,4).
Just like money decimal is an exact datatype, i.e. no subtle rounding
errors at the 8+ digit are introduced. That does not mean there are no
algorithmic rounding errors. I demonstrated a very clear one where
stripping the currency symbol gave 3 euro and 3 dollar the same
values. There are other possible algorithmic errors such as rounding
between operations instead of only at the end. (Or not rounding between
operations if that is what is required for your currency.)
And if they do, is the solution to just be
judicious in the use of Round() functions? or is using Float the
preferred option.
float is never the preferred solution when accuracy matters because it
adds the rounding problems to all the algorithmic problems.
Jochem
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;192386516;25150098;k
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:307192
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
