I have a Windows 2003 server, with SQL 2005. How do i change the culture/regional options for the database as on my local test machine (which seems to be exactly the same in terms of windows regional settings and SQL server settings) the currenency fields show with a £ signs as expected, however on my production box they are $. I know this should be simple but its the night before a deadline and my brain is screwed. Please help!!
View 5 RepliesHi, I have a report which displays monetary amounts. I have set the format of the table cells to c0, and everything is showing in dollars. However, I need this to show as pounds and can't seem to find out how to do this. I have checked the regional settings on my PC which are correctly set to English (United Kingdom). I have also tried going to tools, options in the report and then changing the language to "Same as Microsoft Windows" under International Settings but this does not seem to have done the trick. Also I have to enter date parameters in as mmddyy as opposed to ddmmyy. Does anybody know how I can change these things? Please help, it's driving me up the wall!!
View 5 Replies View RelatedI 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 have select statement that returns data on a construction project.
I have a start date, end date, and forecasted cost for each task in the project. I need to create a table that spreads the dollars in a linear fashion broken down by fiscal period.
I have:
task_id, start_date, end_date, cost
1, 9/15/2008, 12/15/2008, 3000
2, 7/1/2008, 12/15/2008, 550
I need
task_id, fiscal_period, cost
1, 200803, 500
1, 200804, 1000
1, 200805, 1000
1, 200806, 500
2, 200801, 100
2, 200802, 100
2, 200803, 100
2, 200804, 100
2, 200805, 100
2, 200806, 50
I can do the math to properly calculate the dollar amount, I am having trouble creating the statement that will process through each row of my select statement and insert multiple rows into the new table.
This is kind of a follow-up on my previous post "Get 15th and last day trans". Our client has asked for open sales orders dollars for a given date range to include order dollars active as of the 15th of the month and the last day of the month.Â
Below is what I started and I hard-coded the Jan15Open and JanEndOpen so you could see what I am trying to accomplish. So, for example, if I pass @StartDate of '01-01-2013' and @EndDate of '12-31-2014' I could get back results for 01-15-2013, 01-31-2015, 02-15-2013, 02-28-2013, etc. all the way to 12-31-2014. Note that the results will depend on the TransDate datetime column. Once I get the query correct I plan to create a temp table to hold the results so I can report them back in a web page.Â
ALTER PROCEDURE [dbo].[afa_selSalesOrdersOpen]
@StartDatedatetime = null,
@EndDatedatetime = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[Code] ....
Hi,I'm having some trouble with my asp.net page and my sql database. What I'm trying to do is allow the user to upload an number to the database, the number is a money amount like 2.00 (£2.00) or 20.00(£20.00). I've tried using money and smallmoney datatypes but the numbers usually end up looking like this in the database...I enter 2.00 and in the database it looks like 2.00000, and even if I enter the information directly into the database I get the same results. I'm not going to be using big numbers with lots of decimal places like this 1000,000,0000. Can anyone help me? All I want is to know what to set the value to on my aspx page and what setting to set the field to in my database, I'd just like two pound to appear as 2.00. any help would be great.
I'm using Microsoft Visual Web Developer 2005 Express Edition and Microsoft SQL Server 2005 if that helps.
Thanks.
Please i need to display the money column in DataBase in an asp.net page but i get something like this 786.0000 how can i format it so that i get something like 786.00
Thanx
Hi everybody.Here's my Product tableID int,Title nvarchar(50),Price moneyHow can I get value from price field like thisIF PRICE is 12,000.00 it will display 12,000IF PRICE is 12,234.34 it will display 12,234.34Thanks very much...I am a beginner. Sorry for foolish question
View 7 Replies View RelatedArgggg....Please help.I need to store money in SQL Server.I am using C# and a stored procedure to insert.How do you accomplish this? Specifically what datatype should the money variable be in C# during the insert? It's initial value is a string as it is entered from a text field.I have been trying for over an hour now to simply insert money into SQL Server using C# and have it stored as a money type!Thanks
View 3 Replies View Relatedhello everyone...,i have problem in money format...i have moneytable is containning: userid money A 20000,0000 B 40000,0000userid type varchar(50)money type money i have store procedure like this:ALTER PROCEDURE [dbo].[paid]( @userid AS varchar(50), @cost AS money, @message as int="1" output)ASbegin transactiondeclare @money as money select @money = moneyfrom moneytablewhere userid=@useridif (@money > @cost)beginset @money = @money - @costUPDATE moneytable SET money = @money WHERE userid=@useridset @message ='1'endelsebeginset @message = '2' endCOMMIT TRANSACTION when i execute this procedure. i insert value to userid Acost 100,0000 it can not decrease, because cost 100,0000 is same with 1000000, why is like that?i want cost 100,0000 is same with 100. how can i do that? thx...
View 6 Replies View RelatedHi,
I have a field in database money. When I enter value for it the amount entered is for example 20.000. How can I compare this value with noraml vaules i.e. like 20 in my search engine. Will I need to convert it to varchar and then compare it or is there some other way. Also if I need to convert it to varchar, how can I do it?
Thanks in advance,
Uday
Hi All,
Here in Belgium, we work with a comma as decimal seperator, also in all the web apps...
I've tried to update a money table on the sql with following statement
update parts set article = '" & art & "' and price= cast(" & p & " as decimal(5,2)) where supid=202
in this example p is a variable and contains figures like 5,7
This statement always give following error Incorrect syntax near the keyword 'as'
does some have an idea how to fix this one ?
thx
Is there a way to format the output of an calculation so that a comma is used as decimal symbaol and a periode after each 3 digits left of the comma ?
We're calculating salary increase like 5000 * 1.025 and the output should look like 5.250,00
Thanks for any help
Charles
Greetings,
How do you keep or round a money datatype to use only 2 decimal places? I have an instance where I am multiplying a money with a decimal datatype. The decimal has up to 8 decimal places. This is causing the money datattype to extend to 4 decimal places. This makes for problems when I am comparing 2 values.
ex.)
IF 14.88 >= 14.8821
99% of the time this does not happen, but is causing problems. Does anyone have any suggestions?
thanks in advance,
Roger
From query analyzer how can I change the field datatype from money to varchar?
Alter table tablenaame alter column columnname varchar(30)--Is not working.
How can I alter a field datatype from money to varchar?
View 2 Replies View RelatedHi, I have data in a Payment field:
45.14 - was orig in text format.
Now after converting to money, I see it as this:
45.1400
I need to get rid of the zeros! Anyone please help?
Thanks!
Hi everybody.
Here's my Product fields
ID int,
Title nvarchar(50),
Price money
How can I get value from price field like this
IF PRICE is 12,000.00 it will display 12,000
IF PRICE is 12,234.34 it will display 12,234.34
Thanks very much...I am a beginner. Sorry for foolish question
Hi all. Is there a way in SQL to convert the integer to currency format? just for example....
4000 convert to 4,000
1312500 convert to 1,312,500
30000 convert to 30,000
Most of you will say "Do it in your front end"... but the problem is I don't know how to do it in my Report(Business Intelligence Project). If anyone of you knows, tell me please... Thanks. :)
-Ron-
Do you usually use the money or smallmoney datatype for US dollars in a table, or do you use a decimal datatype, and if so which. Any opinions on the advantages and disadvantages of each?
If you use money or smallmoney, do you usually add a constraint to make sure the value is even to the penny (no $24.3487 type amounts)?
CODO ERGO SUM
I'm looking for a way to "secure" a column of MONEY values. The idea is to hide the value without resorting to actual SQL encryption services that would require converting the MONEY to a VARBINARY. In short, I want to keep the column type as MONEY, but I want to scramble the value, so its still a valid MONEY type.
The goal is to have a pair of UDF's SCRAMBLE() and UNSCRAMBLE() that work on MONEY.
I've done some searching, but have not found anything along this topic.
Does anyone have any ideas on how to do this?
Hi!
When I write:
'SELECT Amount FROM tTransaktion'
I get returnvalues such as '12000.0000'. Instead, I want it to return '12 000'.
The Amount column is of datatype money. Is this possible!?
Thanks!
I use asp.net 2.0 and sql server 2005 for a web site (and Microsoft enterprise library).When I run aplication at local there is no problem but at the server I take this error:Disallowed implicit conversion from data type varchar to data type money, table 'dbname.dbo.shopProducts', column 'productPrice'. Use the CONVERT function to run this query.productPrice coloumn format is money. It was working correctly my old server but now it crashed.How can I solve this problem? And why it isn't work same configuration? My code:decimal productPrice;Database db = DatabaseFactory.CreateDatabase("connection");string sqlCommand = "update shopProducts set ......................,productPrice='" + productPrice.ToString().Replace(",", ".") + "',..................... where productID=" + productID + " ";db.ExecuteNonQuery(CommandType.Text, sqlCommand)
View 1 Replies View RelatedHi there,I have a table products with product_price money(8) field. I have a stored procedure,CREATE procedure p_dat_update_product( @product_id int, @product_price decimal) asset nocount ondeclare @msg varchar(255) -- error message holder-- remove any leading/trailing spaces from parameters select @product_id = ltrim(rtrim(@product_id)) select @product_price = ltrim(rtrim(@product_price))-- turn [nullable] empty string parameters into NULLs if (@product_id = N'') select @product_id = null if (@product_price = N'') select @product_price = null-- start the validation if @product_id is null begin select @msg = 'The value for variable p_dat_update_product.@product_id cannot be null!' goto ErrHandler end-- execute the query if exists (select 'x' from product where product_id = @product_id) begin update dbo.product set product_price = @product_price where product_id = @product_id end if (@@ERROR <> 0) goto ErrhandlerreturnErrHandler: raiserror 30001 @msg returnGO And a class function public void Edit_Product(string product_id,string product_price) { decimal dec_product_price = decimal.Parse(product_price); SqlDataServ oMisDb = new SqlDataServ("LocalSuppliers"); oMisDb.AddParameter("@product_id", product_id); oMisDb.AddParameter("@product_price", dec_product_price.ToString()); oMisDb.ExecuteCmd("p_dat_update_product"); }But i keep getting this error,Error converting data type nvarchar to numericoMisDb.ExecuteCmd("p_dat_update_product"); I do not know what to do and solve this. Is there something that i have done wrong.thanks in advance. PS: still learning aspx and ms sql
View 12 Replies View RelatedWhat is the difference between money and smallmoney, and is there a way to format these?
View 3 Replies View RelatedHi,
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?
have a SQL server table with a vairbale unitCost of type money and via a stored procedure I am trying to update it. Through asp I capture the value as a string and then ATTEMPT to convert it into a variable of type money before executing the stored procedure as follows.
SqlParameter parameterUnitCost= new SqlParameter("@unitCost", SqlDbType.Money,8);
parameterUnitCost.Value = unitCost;
myCommand.Parameters.Add(parameterUnitCost);
This gives me an input string error. On the other hand if I change that line to the following, the page works flawlessley:
SqlParameter parameterUnitCost= new SqlParameter("@unitCost", SqlDbType.Money,8);
parameterUnitCost.Value = 25.45;
myCommand.Parameters.Add(parameterUnitCost);
any thoughts???
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,
i'm get some strange behaviour with the money and float type. with a calculator divide 70 by 47 and multiply by 40 (70 / 47 * 40) and you get 59.5744 (rounded to nearest 4 decimal places). now try this in query analyser with the 70 as a money type and the result as a float:
declare @price money
declare @result float
select @price = 70
select @result = @price / 47 * 40
Print 'result = ' + str(@result,18,4)
now this will print out 59.5720
am i missing something ?
hi
When we use money data type and which type data
thanks
kunal
Could someone explain to me what the best practices are for using these three data types? (i.e. when to use them). I thought this would be a simple answer to find, but after looking through books on line, and this forum, I have still not found the answer. Can anyone help?
View 1 Replies View Relatedi'm using query analyzer,
simply trying to use smallmoney as
In my table I have declared as:
payrate smallmoney,
Inserting values into table like this:
'258'
Error recieved is:
Disallowed implicit conversion from data type varchar to data type smallmoney,
What am I doing wrong? Also, ideally i'd like the 258 to be expressed as £258 (english pounds) if anyone can help with that also.
Thanks in advance