This may be an easy question but I've been reading for about a half
hour and experimenting without results.
I simply want the results of my query to display a specific field that
is typed "money" using + and -
The program that consumes the data expects + on positive numbers and -
on negative. I was hoping to do it in the view instead of processing
the results with the VB application that interogates the DB.
Thanks
How to display money type data properly, with commas every 3 digits?
I've tried :
CONVERT (money, ProjectCost, 1) AS ProjectCost
but it gives me the type as 1234.56 no commas in it?
can anyone help me with that?
thanks!
How to display money type data with commas every 3 digits?
I tried :
CONVERT (money, ProjectCost, 1) AS ProjectCost
but it gives me the type as 1234.56 no commas in it?
can anyone help me with that?
thanks!
Happy Friday!
A while since I have posted a question, and this one is probably real easy.
I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1
If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated.
anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?
Hi,
I have a Price column which has 'money' as the data type.
Then I populated some data into the table. I enter '1.00' in the Price column, then I used the following code to get the data:
Label2.Text = "$" + dataSet1.Tables["products"].Rows[0]["price"].toString();
However, the price is displayed as "$1.0000". But I believe it should display "$1.00". So how can I get rid off the two zeros at the end.
regards
hi
i am retriving value from sql server database like
select cast(round(12345674.8658,2,0) as decimal(20,2))
output is 12345674.87
but i want to get like 12,345,674.87
any function is there?
Why does the money data type have 4 decimal places eg £134.3453 or $12.3636
I would have expected it to only have 2.
hi
When we use money data type and which type data
thanks
kunal
I choose a price field as a money data type, but I cannot change thescale of this.the default scale is 4 (it dimmed).how can I change to 2? I only need 2 scale such as $23.33 instead of$23.3344
View 3 Replies View RelatedHow to get rid of the zeros in money type,
i.e. only show dollars, no cents?
I use:
CONVERT(varchar, Price, 1) AS Price
It gives me the result with 2 digits decimals. like 123,456.00
I only need the dollar part, how to get rid of the zeros, Thanks!
Hi,
I'm using the data type "money" in my SQL database and want to convert what's in txtPrice_textBox to the "money" format. I'm currently using the following code:
' objectCym.price = Convert.ToInt16(txtPrice_textBox.Text) '
Will this work? Is there any reason I should stay away from the "Money" data type?
Thanks,
David
Why does SQL add 4 zeros at the end of a money data type? I have to format my strings once they are retrieved because of this. I am not sure if I did something wrong, but shouldn't it only have 2 trailing zero's?
View 1 Replies View RelatedHi, How would I convert(or format) money data type, so the output will be like: 123,456.78 or 12,345.67
(In other words how would I insert a comma which separates hundred from thousand...)
Thanks a lot,
Andy
I'm using the money data type in a field - but (obvious to me) I need the data to contain two decimal places NO MATTER WHAT - even if they are zeros!
I keep putting in "5.20" and I get "5.2".
How do I keep this from happening? I need both decimal places! I thought about using a text field - but that seems to be wasteful and I would have to do a type cast to do numeric computations.
There has to be a way to do this - this IS the purpose of the data type, isn't it??
Thanks for any help!!
Ryan
I write using the SQL ODBC driver from software into a SLQ table called UPSSHIPMENT the format is as followed:
JobNumber varchar 50
Weight real 4
FreightCost varchar 8
TrackingNumber varchar 50
Shipmethod varchar 50
VOIDID varchar 3
I then have a trigger set to update the PACKAGE table as followed
CREATE TRIGGER [UPS] ON dbo.UPSSHIPMENT
FOR INSERT
AS
BEGIN
UPDATE PACKAGE
SET WEIGHT = inserted.WEIGHT,
FREIGHTCOST = inserted.FREIGHTCOST,
TRACKINGNUMBER = inserted.TRACKINGNUMBER,
COMMENTS = inserted.SHIPMETHOD
FROM PACKAGE
INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
WHERE inserted.VOIDID = 'N'
UPDATE PACKAGE
SET WEIGHT = '',
FREIGHTCOST = '0.00',
TRACKINGNUMBER = '',
COMMENTS = 'UPS VOID'
FROM PACKAGE
INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
WHERE inserted.VOIDID = 'Y'
END
The format of the PACKAGE table is as followed
Jobnumber varchar 50
FreightCost money 8
TrackingNumber varchar 50
Comments varchar 2000
Weight real 4
I am getting the following error
---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Error 260: Disallowed implicit conversion from data type varchar to data type money, table 'TESTing.dbo.Package', column 'FreightCost'. Use the CONVERT function to run this query.
---------------------------
OK
---------------------------
How do you use the convert function to change the data before the update? Thank You!
:mad:
Hi Guys,
We are looking for advice on what to do here; we started out with our db holding ex VAT pricing now. Now the problem is the money type can only hold a few decimal places so when we are converting prices to incl vat it can't actually output the correct pricing and it comes our very ugly.
Unfortunately there is no bigmoney data type so we are in a bit of an awkward position
Any idea's?
Hi All,
Is there a way to set a MONEY datatype to a scale (decimal places) of 2?
The default is set to 4 and I can't seem to find any resources on how to change it.
Do I have to add a check constraint to manually round to 2 decimals??
That seems unneccessary.... but if it is, boo-urns to sql server.
thanks!
Here's what I want to do: I've got a table with orders, each order hasa specific discountrate (an int, which represents a percentage). Eachorder consists of 1 or more items in another table, each item in thattable has a price. Now I want to return the full price and thediscounted price (or the discounted amount).Here's a relevant excerpt of the code:------------------------------------------------------------------CREATE TABLE #tmp (OrderID Integer,Price money,Discount money)DECLARE @Discount moneySELECT @Discount =(((SELECT SUM(OrderDetails.Price * OrderDetailsAmount)FROM OrderDetailsWHERE OrderID = @orderID AND CustomerID = @CustomerID)+(SELECT ISNULL(SUM(OrderDetailsSupplement.Price *OrderDetailsAmount),0)FROM OrderDetailsSupplementINNER JOIN OrderDetails ONOrderDetailsSupplement.OrderDetailsID = OrderDetails.OrderDetailsIDWHERE OrderID = @orderID AND CustomerID = @CustomerID))*( @DiscountRate / 100 ))SELECT CustomerFull,SUM(Price) As Price,SUM(Discount) As Discount,SUM (Products) As Products,COUNT(@orderID) As OrdersFROM #tmpGROUP BY CustomerFullORDER BY CustomerFull------------------------------------------------------------------The problem: instead of getting a low number (like 0.57 for instance),I get a 0. Right now I've "solved" this by replacing "( @DiscountRate /100 )" with just "@DiscountRate" and then dividing by 100 in my aspcode, but I'd really like to know what I'm doing wrong.--BVH
View 7 Replies View RelatedI am trying to do some amount calculations.
The amount was declared as nvarchar data type.
I did the following coding. I want to get 2 places right of decimal
but am getting only one place right of decimal.
SELECT
(CAST(EQ_TotQuoteAmnt AS Float(7,2)) -
CAST(EQ_InsFee AS Float(7,2)) - 150) as Inforce_Premium
FROM Quoted
Can someone please help me with the syntax?
Thanks in advance.
M. Khan
I have a table in SQL 2005 with a field that has a value of type 'money'. When values are added, the field has 4 decimal places. Is there a way that I can make it only have 2 decimal places right away? Thanks!!!
View 1 Replies View Relatedwhat is the recommended data type i should use if i want to have a price field that can include "TBA". i can't use smallmoney i suppose, so i should use VARCHAR then validate the String with Visual Studio?
View 1 Replies View RelatedI have some data that I am outputting to a text file:
sum(sales)
where sales is datatype money. If I execute the query with query analyzer I get 4 decimals, but when it is output to a text file it rounds the number
Grid Text File
2182035.9600 2182035.96
961799.2400 961799.24
22104768.1850 22104768.185
If I do cast or convert it to money it does the same thing.
It really doesn't make a material difference for its intended use but I would like to keep it consistent with 4 decimals.
Should data type money allow nulls? Are there valid arguments both pro and con?
Yes, there is the age-old question regarding how one might interpret a NULL found in any column - does it mean the amount is not known or that the amount is zero (in the case of a numeric type)? You get the drift...
Other than that, though - are there any practical considerations an old data hound ought to be aware of?
Hi all!
I want to create a table. One of the columns should be in the data type MONEY with two digits on the right side of the decimal point.
How is that possible?
Thanks a lot and greetings from vienna
landau
I have a special need in a view for a money column to look like money and still be a money datatype. So I need it to look like $100.00 (prefered) or 100.00(can make work).
If I convert like this '$' + CONVERT (NVARCHAR(12), dbo.tblpayments.Amount, 1) it is now a nvarchar and will not work for me.
How can I cast so it is still money? by default the entries look like 100.0000.
They must remain a money datatype.
i was trying to display constraints but i cant display the type, i dont know wat the type is called to display it
View 5 Replies View RelatedI am beginner in SQL Server and this is the first time I tried to use a column in my table with Timestamp data type. When I open my table and enter data in its fields, the timestamp column shows me <binary Data> instead of showing be the timestamp value. I expected to see a kind of hexadecimal number instead. Is it normal? and if yes, How I will be able to display the value of the timestamp.
View 4 Replies View RelatedGood day,
I am wanting to create a rev counter type graph / display in SSRS 2000. Is this possible at all? I have seen there is a plug in from Dundas that will do it but it is very pricey and was hoping there was an inbuilt function to do it or a free add on from Microsoft.
Does anyone know if this is possible and if so how to do it?
Please any help would be greatly appreciated.
Thanks
I simply want to exclude the time from the datetime datatype in my queries and display the date only.
Do I have to piece it together with datepart() or is there a better way?
Thanks in advance for your help.
I have created a Report Parameter, and set the type of this to "Boolean".
This is displayed as a RadioButton with the options of True or False.
Is there anyway to change this to be displayed as a CheckBox?
Thanks,
Kate
Hi,
I have placed a Image Control in ReportViewer , and in design mode the image(.png) is displayed properly and when it is deployed , i am not able to see the image.
whereas there is no problem of images of gif or jpg or bmp or jpeg type. only problem is image of png type.
I have to display the data in the below said formats..Current sample Data in the table and the data type is numeric(23,10)
50.00
0.50
0.00
0.00
To be displayed in the below format
1.25
0.75
0
0
1
I have to map this column in teh report and should dipslay like above.I think if 0.00 is available then it should display as 0..If 1.0 is available then it should display 1.Any value that has postive number after the decimal should display all the values  example : 2.25,3.75,5.06, So in general the solution to display values like 1.75,1,0 we should not dispaly 0 as 0.00 and 1 as 1.00 and 2 as 2.00 and so on...Any Solutions in terms of SQL query  or SSRS expression.
I would like to display a portion of report where there is data or no data
There is data subreport  display  Â
   Product Name Latex Gloves Â
   Product ID   Â
xxxx5678
 There NO data in the subReport
Â
  Product Name             Â
  Product ID  Â