money data type problems
-
Hope this isn't off topic ...
I am developing a Windows.net/C# app with SQL Server 2000 as the backend. I am storing lots of US currency fields in many different tables and am using the SQL money data type for these.
The application should never display more than two decimal places for currency. The app fills ADO.net DataTables using CRUD (stored procedures). DataTable columns that will receive SQL money data types are System.Decimal.
Even if I CAST money into decimal in my SELECT stored procedures as
CAST(Begining_Balance AS Decimal(12,2))
The .net framework Sytem.Decimal still seems to be storing four decimal places. That is when I bind a textbox to the decimal DataTable column I see 154.2100
Anybody else experienced this??
Thank you!
jmatt
-
Oh, yes. I have pulled some hair out over this one. SQL is giving you one thing, but the control is rendering another! This is a formatting problem.
Here:
<asp:TextBox runat="server"
Text='<%# DataBinder.Eval(Container, "DataItem.BasePrice","{0:c}") %>'
ID="Textbox1" NAME="Textbox1">
</asp:TextBox>
Notice the "{0:c}" This is the magic formatting that you are looking for! This tells the control to render currency. But, Wait! you say, "I'm databinding in code-behind!" Ok, then try this!
Textbox1.Text =
string.Format("{0:c}", someClass.SomeMoney);
Once again the magic "{0:c}". Maybe get fancy, even:
Textbox1.Text = "Your Price: " + string.Format("{0:c}", someClass.SomeMoney);
Hope this helps you, bro.
Matthew Spare
Charismaniac Hog,Matthew Spare
ithinkdesigns.com -
On a sidenote:
MONEY can be tricky, when you do calculations other than addition and subtraction. Consider this example:
declare @m1 money, @m2 money, @m3 money
declare @d1 decimal(19,4), @d2 decimal(19,4), @d3 decimal(19,4)
set @m1 = 1.00
set @m2 = 345.00
set @m3 = @m1/@m2
set @d1 = 1.00
set @d2 = 345.00
set @d3 = @d1/@d2
select @m3, @d3
--------------------- ---------------------
.0028 .0029
(1 row(s) affected)
When you calculate this in Excel, you'll get 0,00289855072463768 as the result. So, the result of the money calculation is off by more than 3%. To get around this, you can use a kludge like:
declare @m1 money, @m2 money, @m3 money
declare @d1 decimal(19,4), @d2 decimal(19,4), @d3 decimal(19,4)
declare @f1 float
set @f1 = 1.0
set @m1 = 1.00
set @m2 = 345.00
set @m3 = @m1/(@m2*1.0)
set @d1 = 1.00
set @d2 = 345.00
set @d3 = @d1/@d2
select @m3, @d3
--------------------- ---------------------
.0029 .0029
(1 row(s) affected)
or, even better IMHO, use the DECIMAL datatype. Btw, MONEY is proprietary. So, one might argue to avoid it, if possible.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks Frank. This is good to know. Where does @f1 get used?
set @m3 = @m1/(@m2*1.0)
is this it, to change the datatype in the calculation?
set @m3 = @m1/(@m2*@f1)
Charismaniac Hog,Matthew Spare
ithinkdesigns.com -
Yes, that's right. Sorry, for this little bit bad copy and paste mistake. It's kind of a relikt, while I played with this example and tried to figure out a workaround with MONEY. But since FLOAT and DECIMAL both have a higher precendence than MONEY the 1.0 is enough. So there should actually be no need for another variable.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks a million for your help Matt and Frank!
Matt, the {0:c} formating propoerty is exactly what I needed!
Frank, your point is well taken. The reasons for decimal you point make a lot of sense. Do think there is a concensus on this money vs decimal issue? I will do a search of the forums to see what has been discussed in the past.
Thanks again
jmatt
-
Here on the forum this is seldomly discussed. You have better luck searching the Google Archive, maybe along with the name Steve Kass. Actually I picked this example from a posting by him. But he has a lot more posted on datatypes like DECIMAL, FLOAT or MONEY.
IMHO, there is no binary decision that one datatype is superior to the others. Here's another example on FLOAT and DECIMAL:
declare @a decimal(18,4)
set @a = 0.0003
select
sum(a)*sum(a)*100,
100*sum(a)*sum(a)
from (
select @a a
union all
select @a
) x
go
declare @a float
set @a = 0.0003
select
sum(a)*sum(a)*100,
100*sum(a)*sum(a)
from (
select @a a
union all
select @a
) x
go
FLOAT seem to calculate correct, right? Which one is better? It still depends on your calculations you wish to do on the server. And a last one:
SELECT SUM(1 / 12331.0) AS X
FROM Northwind..Orders
SELECT SUM(1.0 / 12331.0) AS X
FROM Northwind..Orders
SELECT SUM(1.0 / 12331) AS X
FROM Northwind..Orders
SELECT SUM(1e0 / 12331) AS X
FROM Northwind..Orders
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks again Frank. If only life were simple ... but then again we wouldn't have a job.
OK I am going to push my luck here knowing the following is a .net framework question, not sql but here goes:
Matt's suggestion of
myTextBox.Text = string.Format("{0:c}",someObject.ToString())
works like a charm. But what about binding to a DataView?
myTextBox.Add(new Binding("Text",myDataView,"Balance");
I have tried
myTextBox.Add(new Binding("Text",myDataView,string.Format("{0:c}","Balance"));
and
myTextBox.Add(new Binding(string.Format("{0:c}","Text"),myDataView,"Balance");
both compile and run but do not result in two decimals in the textbox.
Any .net experts out there?
Thank you
-
Are your myTextBox's being generated through code or are they on the page already? Is this a user control and is the skin seperate? Is this a class being imported? The problem you are running into is based on implementation versus presentation. Presentation is where the extra .xx00's are being introduced and .Net has many ways to implement and many ways to present. Can you give me more code to look at (.aspx, .ascx, aspx.cs, .cs) ? You can email if you like, and mask any proprietary info.
Charismaniac Hog,Matthew Spare
ithinkdesigns.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply