linkcube1
asked on
Converting to 2 decimal places in sql query
In the following code below I want to take 80% of the price for each item and return a total amount. It does this fine but doesn't round to 2 decimal places I'm using SQL CE edition so I can only use money as the field type.
I have tried this "("SELECT BookID, MemberID, Title, Author, (Price=Round([Price],2) * 0.8) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'", con);"
but the select isn't returning anything so I assume its wrong. Any tips or suggestions would be great.
I have tried this "("SELECT BookID, MemberID, Title, Author, (Price=Round([Price],2) * 0.8) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'", con);"
but the select isn't returning anything so I assume its wrong. Any tips or suggestions would be great.
public static void LoadCashOutList(ListView lv, Member m, TextBox txtTotal, Label label1)
{
SqlCeConnection con = BookStoreDB.GetConnection();
SqlCeCommand comm = new SqlCeCommand("SELECT BookID, MemberID, Title, Author, (Price * 0.8) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'", con);
con.Open();
SqlCeDataReader reader = comm.ExecuteReader();
decimal cashOutTotal = 0;
while (reader.Read())
{
string[] data = new string[5];
data[0] = reader["BookID"].ToString();
data[1] = reader["MemberID"].ToString();
data[2] = reader["Title"].ToString();
data[3] = reader["Author"].ToString();
data[4] = reader["Price"].ToString();
cashOutTotal += Convert.ToDecimal(reader["Price"]);
ListViewItem liv = new ListViewItem(data);
lv.Items.Add(liv);
}
label1.Text = " Hello " + m.FirstName + " " + m.LastName + ", Here are your books avaliable for cashing out";
txtTotal.Text = "$ " + cashOutTotal.ToString();
con.Close();
}
Try:
"SELECT BookID, MemberID, Title, Author, Round([Price]* 0.8,2) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'"
"SELECT BookID, MemberID, Title, Author, Round([Price]* 0.8,2) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'"
ASKER
Its still returning more than 2 places :om_prakash_p:
You can use the following query for converting price with two decimals.
SELECT BookID, MemberID, Title, Author, CONVERT((Price * 0.8), float, 0) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'",
SELECT BookID, MemberID, Title, Author, CONVERT((Price * 0.8), float, 0) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'",
data[4] = String.Format("{0:0.00}", reader.GetDouble(reader.ge
sorry one type mistake above. So here is your final query.
SELECT BookID, MemberID, Title, Author, CONVERT(money, (Price * 0.8), 0) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'"
There are three styles to use in Convert as given below. For example @value is 4532.784
CONVERT(money, @value, 0) will return 4532.78
CONVERT(money, @value, 1) will return 4,532.78
SELECT BookID, MemberID, Title, Author, CONVERT(money, (Price * 0.8), 0) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'"
There are three styles to use in Convert as given below. For example @value is 4532.784
CONVERT(money, @value, 0) will return 4532.78
CONVERT(money, @value, 1) will return 4,532.78
And what about CONVERT(money, @value, 0)??
Kindly have a look here for CONVERT function detail.
I hope it will help.
Kindly have a look here for CONVERT function detail.
I hope it will help.
ASKER
Same result with 0 three decimal places.
hmmmmmmm.
its mean you price column is also MONEY type. kindly try this one. It will give you the result :)
convert(varchar, @value, 0)
its mean you price column is also MONEY type. kindly try this one. It will give you the result :)
convert(varchar, @value, 0)
ASKER
Converting brings me back to .0000 decimal places
I have checked at my side and it works.
declare @value money
set @value = 4532.784
select convert(varchar, @value, 0)
declare @value money
set @value = 4532.784
select convert(varchar, @value, 0)
ASKER
I'm using compact edition so I dont have Varchar, so im using nvarchar but that shouldn't make a difference.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>It does this fine but doesn't round to 2 decimal places I'm using SQL CE edition so I can only use money as the field type. <<
Unfortunately money is always going to have 4 decimal places.
Unfortunately money is always going to have 4 decimal places.
ASKER
ok the String format works great, but how to I format this line
data[4] = reader["Price"].ToString() ;
Thanks for the help guys!
data[4] = reader["Price"].ToString()
Thanks for the help guys!
ewangoya gave the solution to that bit further back up the thread
data[4] = String.Format("{0:0.00}", reader.GetDouble(reader.getOrdinal("Price")));
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Use math.Round () function in C#, VB
or ROUND in SQL
or ROUND in SQL
You can use String.Format
String.Format("{0:0.00}", YourValue);