Money Data Type: field

Showing posts with label field. Show all posts

Friday, March 30, 2012

Wednesday, March 28, 2012

more empty fields

I will explain the problem as best I can(bad English, sorry)
There are 2 tables in the database, there is 1 field with the same name in both tables.
The report shows information based on this common field witch I use to filter the data.
As soon as the common field is not yet in the second table because the user has not yet placed data there with the common field the reports flips totally blank.

I hope this explanation helps, this problem bugs me for some time and I have to make more reports based on this database structure.

ThanksYou need a left outer join from the first table to the second one.

Monday, March 26, 2012

More Bytes returned from Image Data Type

I have a field set to Image data type, and store a long string(possible othe
r
objects)into it with size 5663, and returned with 5663+28 bytes.
These strings wil be deserialized after retrieving. However failde because
the bytes seems to include some extra bytes with \0 and 0x01 and have 28 mor
e
bytes ahead of the original string.
How could I deal with it? Just remove the first 28 bytes? It this method
formal? I had thought the returned bytes should have the same size with the
one I stored into, it seems not, am I right?
Thanks.BOL talks about "BLOBs and OLE Objects" and the normal process for images wa
s
to read in "chunks" (e.g. Sussman's ADO 2.6 Wrox book), but now should use
Stream object instead - check your doc [you omitted to say what
language/version you were using]
HTH
Dick
"zhaounknown" wrote:

> I have a field set to Image data type, and store a long string(possible ot
her
> objects)into it with size 5663, and returned with 5663+28 bytes.
> These strings wil be deserialized after retrieving. However failde because
> the bytes seems to include some extra bytes with \0 and 0x01 and have 28 m
ore
> bytes ahead of the original string.
> How could I deal with it? Just remove the first 28 bytes? It this method
> formal? I had thought the returned bytes should have the same size with th
e
> one I stored into, it seems not, am I right?
> Thanks.|||I am using C#, in .Net framework 1.1.
What documentation I shoudl refer to, since I can't find one addressing
this, partly because I am new to the BLOB data type.
However, how the underlying BLOB field is implemented doesn't matter to the
queried result for a BLOB field, is that right?
Please give more detailed guidance for this.
Thank you very much.
"Dick in UK" wrote:
> BOL talks about "BLOBs and OLE Objects" and the normal process for images
was
> to read in "chunks" (e.g. Sussman's ADO 2.6 Wrox book), but now should use
> Stream object instead - check your doc [you omitted to say what
> language/version you were using]
> HTH
> Dick
> "zhaounknown" wrote:
>

Friday, March 23, 2012

Month value in SQL

Hi all

Does anyone know how to get the month field from a date in a SQL statement? I am using Oracle 8.0

There is a field called "birth_date" in my table and I have to print the number of records group by month as follows:

Month Count
--- ---
1 45
2 187
3 18
. ..
. ..
. ..
etc

I need the month value in 1-12 format. Please help..

-Chinnaselect month(birth_date),count(*)
from yourtable
group by month(birth_date)

rudy|||There is not "month" function in Oracle!
that shoulde be:
select to_char(brith_day,'mm'),count(*) from yourtable group by to_char(brith_day,'mm')|||select to_char(sysdate,'MON') from dual;

will return today's month|||MONTH() is ANSI/ISO standard sql

my apologies, Chinna, i overlooked the fact that you said oracle 8

oracle corp, in its infinite wisdom, apparently did not begin supporting standard sql until release 9

:(

Month Format

Hello Experts!

I am trying to format the datetime field in my grouping. Right now I am using the expression =Month(Fields!TestDateTime.Value) in the edit group properties and it returns (for example) May 01 for the group. I would like it to show up as May 2007 instead. Would someone be kind enough to post what the correct expression should be?

Thanks,

Clint

Not sure if there is a better way, but this works:

Code Snippet

=MonthName(Month(Fields!DateOrderCreated.Value)) + " " + format(Fields!DateOrderCreated.Value,"yyyy")

|||Thanks, but that also does not work. It returns the same "May 01".|||What datatype is the field you are formatting?|||It is a datetime data typ|||

If it is a datetime data type, then you can use Format() on it using any of the standard formatting codes, like this:

Code Snippet

=Format(myDataField, "Mon yyyy")

|||Hmmmm still not working. What area should the code be put in to? I have tried the group properties, the field property value, and the custom format are of the field to no avail. It will either error out or return the same "May 01". I am working from the report layout area of the reporting services.|||LOL. Found the answer. Simply in the Format area, I just put a "Y" in the custom format field. thanks so much for your help though.

Month and date wrong way around

Hi,

I am querying a report that was written with reporting services, via a webpage in vs2005. However when I input a date field to query from i.e 14/01/2007, this produces an error because the report is seeing this as 01/14/2007, even though in the database the record shows 14/01/2007? Please can someone offer any advice what to check?

thanks,

Harry.

Is that report runs client side?

|||

It seems to happen when report is tested in 'preview mode', and when the report is deployed it happens on the client. An error results because it can't handle the DD/MM the wrong way around?

There must be something I'm missing here...?

Many thanks,

Harry

|||

Hi, Harry:

You should try to format your date before you using them.

You can check out this article about how to format the date in SSRS.

http://msdn2.microsoft.com/en-us/library/ms157328.aspx

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Hi, Confused

I'm a little confused how I can use the conversions,

I'm passing StartDate AND EndDate from 2 txtboxes,, my data is SELECT par1, par2, par2 from tbldatabase1 WHERE par1 >@.StartDate and par1 <@.EndDate.

But the parameters from the date boxes are taken in the wrong wat around. Do you know how I can implement some code to change this?. My main visual studio pages are written in c#?.

Or would you do this in the query itself?

Any help would be greatly appreciated.

Thanks Harry.

|||

HI,camper :

If your referring toReport Manager displaying the DateTime in theparameter input box, then no, you cannotformat this. You can onlyformat the date within thereport itself, or like the example I posted as following, give the user a dropdownparameter list of dates.

Apparently you can set theparameter to a string and then it won't enter the time, though you'll have to convert it into a date before it runs against your dataset. This way though,report users can enter a non-date as aparameter.

For some of myreports I create aparameter dataset based on the table holding the dates.

SELECT DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField) AS Label, MyDateField AS Value
FROM MyTable
GROUP BY DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField), MyDateField
ORDER BY MyDateField

This creates 2 fields, 'Label' which is what the user selects from and 'Value' which the dataset uses to query on.
Create aparameter (@.MyParameterDate) and reference it against your main dataset e.g.

SELECT * FROM MyTable WHERE MyDateField = @.MyParameterDate

In yourReportparameters set the values to be from a query and select yourparameter dataset. Set the Value and Label datafields to the ones created above and ensure theparameter datatype is DateTime. You can change the aboveformat if you don't want dates displaying as '7 November 2005'

I hope the above information will be helpful. If you have any issues or concerns, please let

me know. It's my pleasure to be of assistance

|||

Hi,

I know it's a few months since the last entry but I was having the same problems and fixed it by setting the Report properties: Language Property to my locale (English (United Kingdom) in this case). You can get at these properties from the report designer (in local mode) and clicking on the grey area outside the design grid. This stopped the report from swapping the months and days around :-)

Good luck.

|||

Change this line in your model's smdl file

In this case German yyyy.mm.dd

|||

I am having the same problem. I saw your solution and tried that. However, it appears that those settings do not get passed to the report server. The report now correctly allows entry of the date in the Visual Studio designer, and when selected shows the date correctly, however, when the report is delpyed, the server, does not change behavior. It still thinks it is using mm/dd/yyy format.

Any ideas how to change the server side format to allow dd/mm/yy parameter format entry?

|||I also had to change regional settings to YYYY/MM/DD format on server. In layout view click properties on righthand side and select report from available fields, Click on language and choose English South Africa for YYYY/MM/DD format.sql

Friday, March 9, 2012

Monitor table changes without triggers.

Hi all.
Is there a way to know when data is updated or inserted into a table without
the use of triggers or any internal field?
Thanks and regards.Hi
Only if you use SQL Server 2005
"Fabio Reynoso" <Fabio Reynoso@.discussions.microsoft.com> wrote in message
news:D5B2CD68-5131-48FB-A25D-400514124AAD@.microsoft.com...
> Hi all.
> Is there a way to know when data is updated or inserted into a table
> without
> the use of triggers or any internal field?
> Thanks and regards.|||Thanks for the help.
How can it be done. Please some advice will be great.
Regards.
"Uri Dimant" wrote:
> Hi
> Only if you use SQL Server 2005
>
> "Fabio Reynoso" <Fabio Reynoso@.discussions.microsoft.com> wrote in message
> news:D5B2CD68-5131-48FB-A25D-400514124AAD@.microsoft.com...
> > Hi all.
> >
> > Is there a way to know when data is updated or inserted into a table
> > without
> > the use of triggers or any internal field?
> >
> > Thanks and regards.
>
>|||Read about "Query Notifications".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Fabio Reynoso" <FabioReynoso@.discussions.microsoft.com> wrote in message
news:7C34D8F0-74DC-4615-88CB-7994FBBFF3D7@.microsoft.com...
> Thanks for the help.
> How can it be done. Please some advice will be great.
> Regards.
> "Uri Dimant" wrote:
>> Hi
>> Only if you use SQL Server 2005
>>
>> "Fabio Reynoso" <Fabio Reynoso@.discussions.microsoft.com> wrote in message
>> news:D5B2CD68-5131-48FB-A25D-400514124AAD@.microsoft.com...
>> > Hi all.
>> >
>> > Is there a way to know when data is updated or inserted into a table
>> > without
>> > the use of triggers or any internal field?
>> >
>> > Thanks and regards.
>>

Wednesday, March 7, 2012

monitor large update

I have a large table 34 million records and I need to update a field.
I am doing this in batches of 1 million records (by joining a temp
table to the primary key).
I am trying to monitor the update using
SELECT COUNT(*)
FROM myTable with(nolock)
where newKey is null
What is weird is the number counts down a few million (one to many
relationship) records then all of the sudden the number is bumped back
up to 34 million records again and the update statement (same
transaction) seems to start over.
Any ideas on what is going on?
Is my with(nolock) method an invalid way to monitor the update?
Thanks for the advice!If you are updating in batches within a loop, then you should have a count
of the current iteration. Perhaps have the SP display some status
information while executing in Query Analyzer. If <print @.loop> doesn't
display until the end of the transaction, then try <select @.loop>. You can
also monitor using SQL Profiler.
To improve the performace of this monster update try the following:
#1 Insert the records with the columns already populated with non-null
values. This way your update does not need to extend the data when updating.
#2 Consider setting recovery model to simple.
#3 Drop all indexes from table before the update and re-create them again
afterward.
#4 Do not include updatable columns in a clustered index.
<daveg.01@.gmail.com> wrote in message
news:1122495806.739965.230420@.z14g2000cwz.googlegroups.com...
>I have a large table 34 million records and I need to update a field.
> I am doing this in batches of 1 million records (by joining a temp
> table to the primary key).
> I am trying to monitor the update using
> SELECT COUNT(*)
> FROM myTable with(nolock)
> where newKey is null
> What is weird is the number counts down a few million (one to many
> relationship) records then all of the sudden the number is bumped back
> up to 34 million records again and the update statement (same
> transaction) seems to start over.
> Any ideas on what is going on?
> Is my with(nolock) method an invalid way to monitor the update?
> Thanks for the advice!
>|||Hi
You may want to create an indexed view
Some examples from Steve Kass
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
<daveg.01@.gmail.com> wrote in message
news:1122495806.739965.230420@.z14g2000cwz.googlegroups.com...
>I have a large table 34 million records and I need to update a field.
> I am doing this in batches of 1 million records (by joining a temp
> table to the primary key).
> I am trying to monitor the update using
> SELECT COUNT(*)
> FROM myTable with(nolock)
> where newKey is null
> What is weird is the number counts down a few million (one to many
> relationship) records then all of the sudden the number is bumped back
> up to 34 million records again and the update statement (same
> transaction) seems to start over.
> Any ideas on what is going on?
> Is my with(nolock) method an invalid way to monitor the update?
> Thanks for the advice!
>|||Could you expain that a bit? Will this allow me to monitor a large
update that might take 2 or 3 hours?
Does the clustered index on the view get updated while the update on
the base table is going on?

Monday, February 20, 2012

Money to Varchar

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.Can you describe "not working" in a bit more detail? Does the command appear to run, but the column datatype doesn't change? Does it give an error message? Does your server crash and spit blue smoke?

-PatP|||You cannot change the datatype to money from (n)varchar, because this conversion cannot be done implicitly. You'll have to create a new table with desired datatype (money), use CONVERT/CAST function to insert the data into it, then drop the original table, create a new table with the same name, and insert the data back in.

Money to Varchar

How can I alter a field datatype from money to varchar?To change the column type you will have to create a new table and copy the data over using the convert function, example below.

--create table x
--(a int null,
--b money null)

CREATE TABLE dbo.Tmp_x
(
a int NULL,
b nvarchar(50) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.x)
EXEC('INSERT INTO dbo.Tmp_x (a, b)
SELECT a, CONVERT(nvarchar(50), b) FROM dbo.x')
GO
DROP TABLE dbo.x
GO
EXECUTE sp_rename N'dbo.Tmp_x', N'x', 'OBJECT'
GO|||You can also add a temporary column to your table of the varchar type and fill it with the data from the money column to see if all values can be converted. If so, empty the money column, change the datatype of the money column to varchar and fill it with the data of the temporary column. Finally delete the temporary column.

To add a column:

alter table owner.your_table
add temp_column varchar(10)

To modify a column datatype:

alter table owner.your_table
alter column your_column varchar(10)

To drop a column:

alter table owner.your_table
drop column temp_column

money in stored procedure

Hi 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
)
as
set nocount on

declare
@.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 Errhandler

return
ErrHandler:
raiserror 30001 @.msg
return
GO

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 numeric

oMisDb.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

the stored proc accepts int and decimal

priteshchandra:
CREATE procedure p_dat_update_product(
@.product_id int,
@.product_price decimal
)

while you are passing string

priteshchandra:
public void Edit_Product(string product_id,string product_price)

so you need to cast them to appropriate datatypes

priteshchandra:
oMisDb.AddParameter("@.product_id", product_id);
oMisDb.AddParameter("@.product_price", dec_product_price.ToString());

should be

oMisDb.AddParameter("@.product_id", convert.toint32(product_id));
oMisDb.AddParameter("@.product_price", convert.toDecimal(dec_product_price));

hope that helps.

thanks,

satish.

|||

I tired ur solution but got this error.

Compiler Error Message:CS0103: The name 'convert' does not exist in the current context

oMisDb.AddParameter("@.product_price", convert.toDecimal(dec_product_price));

|||

aargh my friend

Convert.ToDecimal() , do you've .net documentation/MSDN or not?

its straight forward naming convention of C#.

thanks,

satish.

|||

I think he meant Decimal.Parse() and Int32.Parse()?

|||

Motley:
I think he meant Decimal.Parse() and Int32.Parse()?

yes infact TryParse would be efficient Smile .

|||

Just replace this line:

oMisDb.AddParameter("@.product_price", dec_product_price.ToString());

with this:

oMisDb.AddParameter("@.product_price", dec_product_price);

because you already converted it to decimal here:

decimal dec_product_price = decimal.Parse(product_price);

|||

kipo:
Just replace this line:
oMisDb.AddParameter("@.product_price", dec_product_price.ToString());
with this:
oMisDb.AddParameter("@.product_price", dec_product_price);
because you already converted it to decimal here:
decimal dec_product_price = decimal.Parse(product_price);

I tired this before and i kept getting this error,

CS1502:The best overloaded method match for'MISUtilities.DataServices.SqlDataServ.AddParameter(string, string)'has some invalid arguments

Source Error:

Line 359: oMisDb.AddParameter("@.product_price", dec_product_price);

|||

satish_nagdev:
aargh my friend
Convert.ToDecimal() , do you've .net documentation/MSDN or not?
its straight forward naming convention of C#.
thanks,
satish.

Sory, i do not have MSDN. am using Express Edition for now since me learning .NET

|||

I believe you are looking for this syntax:

oMdb.Parameters.Add("@.param",SqlDbType.Int).Value={something}

oMdb.Parameters.AddWithValue("@.param", {something})

of course, replace {something} with a constant, variable, etc.

|||

After working to finding a solution, i kept failing. eventually i came up with this.

i changed the field from money to nvarchar and everything worked fine on wards.

question:

replacing money with nvarchar, will it affect my data in any way already stored?

|||

Hi Pritesh,

you said money datatype and in actual post i could see you are accepting decimal datatype. i think that is the actual problem.

regarding replacing money with nvarchar is not a good idea. there might be cases when you need to work on numeric (money) so you'll do conversion back and forth to money and varchar.

so in your table the column is of type money right? if yes modify your sp to accept money instead of decimal and in c# code use overloaded parameter constructor and pass datatype like sqlint etc

thanks,

satish.

|||

well, i tired placing money in the stored procedure but i kept getting error that money is not valid in sp.

money format ..need some help..please

Hi everybody.
Here's my Product table

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

Formatting is generally applied at the page where the data is going to be displayed as opposed to altering the datasource itself.

If you were going to databind your sql data to something such as a GridView, then you can alter the data's format by specifying a DataFormatString.

for currency you can use: {0:c}
for numeric data with 2 digits after the decimal you can use: {0:n2}

For example:

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="Price" DataFormatString="{0:c}" HtmlEncode=False HeaderText="With Currency Symbol" /> <asp:BoundField DataField="Price" DataFormatString="{0:n2}" HtmlEncode=False HeaderText="Without Currency Symbol" /> </Columns></asp:GridView>

If you are actually looking to alter the data in sql server, please let us know.

|||

Thanksmbanavige very much for your answer. I use DataList to display my items and I want to display1 pricein my page. I try to use this code

<%If((Eval("Price") *100) mod 100 >0) { %>

<%#Eval("Price","{0:N2}")%>

<% } else {

%> <%#Eval("Price","{0:c}") %>

<% } %> . But it seem wrong. How can I do something like that ?

|||

I'm not sure i follow what you're trying to do with that code. If you want one price, then choose only one of the sample formats i provided

use this: <%#Eval("Price","{0:c}") %>

or this: <%#Eval("Price","{0:N2}")%>

but not both at the same time.

Unless you're dealing with unpredictalbe currencies, i'd probably just go with: <%#Eval("Price","{0:c}") %>

|||

mbanavige:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="Price" DataFormatString="{0:c}"
HtmlEncode=False HeaderText="With Currency Symbol" />
</Columns>
</asp:GridView>

Hi Mike,

What would cause a page with say ten colums, all formated in the same manner, to only have five colums display currency correctly and the remainder to just be numbers?

I have this weird scenario going on and it is not a syntax error, as I have checked in dozens of times.

|||

Did you set HtmlEncode to False for all the columns?

|||

Yes I did. I just did a character by character study of two similar pages. The first page is for one State the next page for a different State. Every single character is identical. Therefore I have no option to conclude that there must be some fundamental change in the SQL database tables themselves. each State has it's own table. I will go in and make sure that each field is set to exactly the same data type.

If there is any other possibility that you can think of, please let me know. I am certain that this is not a code issue, the code is working, the reason I am not getting the formatting to work is probably because what is expected in the DB table, is something else. I'll drop you a line after I have checked the tables.

|||

Oaky, I was correct, the problem was in the database. The fields in question on the working table were 'money' and the same fields in the non-working table were 'float'. The instant I changed over to 'money', the web pages formatted perfectly.

There is an interesting sideline to this. In the beginning all state tables were identical, and the database itself was hosted on remote servers in Dallas, Texas in the USA. The database is now hosted on remote servers in Sydney, Australia and when the databse was transferred it somehow mysteriously changed. The technique of restoring a backup on the new server, then attaching it, was how we did the transfer, but it was not 100% flawless. We could not then nor now figure out why some very minor changes occured, perhaps there may have been some slight version differences on server software.

At any rate it's all fixed now, so the moral to the tale is this, don't always assume your code is incorrect, sometimes, it might be other things, as in this case, an issue with the database.

Best wishes.

money format

Hi.
I have a money field and its value is greater than a thousand.For example its value = 32.885,60
I want to show the field's format like this (this number's format).
I mean the thousand separator should be .(dot) and the decimal separator should be ,(comma)
And I want two digit after the decimal separator.All these conditions matches with this number(32.885,60)
Could you help me?

hi muhsin,

write an embeddede function which returns your amount like this: amount.ToString("N")

|||

Dear,

Write the embedded fucntion (=format(field,'fomat')

from

sufian

|||Can you write for me please?
I tried but it didn't work.
What should I write in the format style area?|||

Could you format the string in code and then do a string replace to replace the , with a . and the . with a ,?

|||

Hi Muhsin,

sorry this isn't an answer for you, but very curious as to the currency you are working in?

I am used to formatting it the other way around (32,885.60)

|||

this is your function:

shared function GetCurrencyFormat(byVal Amount As Double) As String

return Amount.ToString("N")

end function

to call the function go to your layout and write in the cell or textbox or whatever the following:

=Code.GetCurrencyFormat(Amount)

thats it

|||

SpaceCadet wrote:
this is your function:
shared function GetCurrencyFormat(byVal Amount As Double) As String
return Amount.ToString("N")
end function
to call the function go to your layout and write in the cell or textbox or whatever the following:
=Code.GetCurrencyFormat(Amount)
thats it

But this is not I want.
This is already a format in the properties of the textbox.
You don't need to write a function for this.
I want the thousand seperator to be dot and the decimal seperator to be comma.
Your solution's result is for example 15,250.30.
But I want 15.250,30.
Thanks anyway.
If you find another solution , please share it with me!|||You can accomplish this by adjusting the Language property for the textbox, or the entire report to a language that uses this format. The language settings can be specific to just number formats when just setting the NumeralLanguage property on the textbox. After setting these properties, just use "N" as the format code.

See the following link for more information about International Considerations for Reporting Services:
http://msdn2.microsoft.com/en-us/library/ms156493.aspx

money format

hi muhsin,

write an embeddede function which returns your amount like this: amount.ToString("N")

|||

Dear,

Write the embedded fucntion (=format(field,'fomat')

from

sufian

|||Can you write for me please?
I tried but it didn't work.
What should I write in the format style area?|||

Could you format the string in code and then do a string replace to replace the , with a . and the . with a ,?

|||

Hi Muhsin,

sorry this isn't an answer for you, but very curious as to the currency you are working in?

I am used to formatting it the other way around (32,885.60)

|||

this is your function:

shared function GetCurrencyFormat(byVal Amount As Double) As String

return Amount.ToString("N")

end function

to call the function go to your layout and write in the cell or textbox or whatever the following:

=Code.GetCurrencyFormat(Amount)

thats it

|||

SpaceCadet wrote:
this is your function:
shared function GetCurrencyFormat(byVal Amount As Double) As String
return Amount.ToString("N")
end function
to call the function go to your layout and write in the cell or textbox or whatever the following:
=Code.GetCurrencyFormat(Amount)
thats it

Money field

I am using Sql Server reporting services 2000. I have a money field which should show postive or negative amount. I used the format as currency. But it shows as ($40.00) for negative amount(it is placing it in braces) and $40.00 for positive amount. How can I show -$40.00??

HI, Nissan:

You can use this expression instead: =FormatCurrency(Fields!UnitPrice.Value,,,False,)

If i misunderstand you about your question, please feel free to correct me and i will try to help you with more information.

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

HI, Nissan:

We are marking this issue as "Answered". If you have any new findings or concerns, please feel free to unmark the issue.
Thank you for your understanding!

Money datatype Substring

I have a sql query listed below, I would like to count the number of values
that
the first 5 characters are '80438' from iNum field.
Please help me correct the sql query listed below.
Thank You,
SELECT count(iNum) from Call_Movement
where substring(cast(iNum as money) as char(20),1,5) like '80438'
iNum Money Format
iNum Data In Call_Movement Table
803482000146220.0000
803482000147143.0000
803482000153805.0000Try,
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
go
Expect a table or index scan.
AMB
"Joe K." wrote:

> I have a sql query listed below, I would like to count the number of value
s
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe
create table #test (c1 varchar(100))
insert into #test values ('80438hwdgyde5787kioui')
insert into #test values ('5838hwdgyde5787kioui')
insert into #test values ('80438mjji')
select count(c1) from #test where c1 like '80438%'
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A5FC4F29-928C-4D6D-B8F6-4A964D6711B2@.microsoft.com...
> I have a sql query listed below, I would like to count the number of
> values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||I received the following error when applying the query listed below.
Please help me resolve the query error listed below.
Thanks,
Error:
Implicit conversion from data type money to varchar is not allowed. Use the
CONVERT function to run this query.
SQL Query:
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
________________________________________
___________________________
"Joe K." wrote:

> I have a sql query listed below, I would like to count the number of value
s
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe,
SELECT count(iNum)
from Call_Movement
where convert(varchar(50), iNum, 0) like '80348%'
AMB
"Joe K." wrote:
[vbcol=seagreen]
> I received the following error when applying the query listed below.
> Please help me resolve the query error listed below.
> Thanks,
> Error:
> Implicit conversion from data type money to varchar is not allowed. Use th
e
> CONVERT function to run this query.
> SQL Query:
> SELECT count(iNum)
> from Call_Movement
> where ltrim(iNum) like '80348%'
> ________________________________________
___________________________
> "Joe K." wrote:
>

Money datatype Substring

I have a sql query listed below, I would like to count the number of values
that
the first 5 characters are '80438' from iNum field.
Please help me correct the sql query listed below.
Thank You,
SELECT count(iNum) from Call_Movement
where substring(cast(iNum as money) as char(20),1,5) like '80438'
iNum Money Format
iNum Data In Call_Movement Table
803482000146220.0000
803482000147143.0000
803482000153805.0000Try,
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
go
Expect a table or index scan.
AMB
"Joe K." wrote:
> I have a sql query listed below, I would like to count the number of values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe
create table #test (c1 varchar(100))
insert into #test values ('80438hwdgyde5787kioui')
insert into #test values ('5838hwdgyde5787kioui')
insert into #test values ('80438mjji')
select count(c1) from #test where c1 like '80438%'
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:A5FC4F29-928C-4D6D-B8F6-4A964D6711B2@.microsoft.com...
> I have a sql query listed below, I would like to count the number of
> values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||I received the following error when applying the query listed below.
Please help me resolve the query error listed below.
Thanks,
Error:
Implicit conversion from data type money to varchar is not allowed. Use the
CONVERT function to run this query.
SQL Query:
SELECT count(iNum)
from Call_Movement
where ltrim(iNum) like '80348%'
___________________________________________________________________
"Joe K." wrote:
> I have a sql query listed below, I would like to count the number of values
> that
> the first 5 characters are '80438' from iNum field.
> Please help me correct the sql query listed below.
> Thank You,
> SELECT count(iNum) from Call_Movement
> where substring(cast(iNum as money) as char(20),1,5) like '80438'
> iNum Money Format
> iNum Data In Call_Movement Table
> 803482000146220.0000
> 803482000147143.0000
> 803482000153805.0000
>|||Joe,
SELECT count(iNum)
from Call_Movement
where convert(varchar(50), iNum, 0) like '80348%'
AMB
"Joe K." wrote:
> I received the following error when applying the query listed below.
> Please help me resolve the query error listed below.
> Thanks,
> Error:
> Implicit conversion from data type money to varchar is not allowed. Use the
> CONVERT function to run this query.
> SQL Query:
> SELECT count(iNum)
> from Call_Movement
> where ltrim(iNum) like '80348%'
> ___________________________________________________________________
> "Joe K." wrote:
> >
> > I have a sql query listed below, I would like to count the number of values
> > that
> > the first 5 characters are '80438' from iNum field.
> >
> > Please help me correct the sql query listed below.
> >
> > Thank You,
> >
> > SELECT count(iNum) from Call_Movement
> > where substring(cast(iNum as money) as char(20),1,5) like '80438'
> >
> > iNum Money Format
> >
> > iNum Data In Call_Movement Table
> > 803482000146220.0000
> > 803482000147143.0000
> > 803482000153805.0000
> >

Money datatype- simple question!

Hi there!
In SQL server 2000, how do you change the number of decimal digits of money datatype field? By default it is 4 digits. I want to have only 2 digits after decimal point. Please help me!Instead of using money, you can use decimal with a scale of 2 or cast the money type to decimal with a scale of 2.|||Hi there!
Thanks for your reply. Now that I have changed the money datatype to decimal.

But the following line of code gives an 'Type Mismatch' error.

Dim Amt
Amt=rs("Rate")+rs("Interest") 'error in this line
Response.write Amt

'Rate' and 'Interest' are fields of decimal datatype.
rs - is a ADO recordset.
Can you tell me why? I have tried with 'Numeric' datatype also, and got the same error.|||Since your using VB, why not leave it as money and then use the FORMAT function?

MyValue = Format(DBValue,"###,###.##")

OR

In SQL Server you could use the CAST function on the MONEY field

try this

declare @.amt money

set @.amt = 0.2
select CAST(@.amt AS VARCHAR)|||It looks like you are doing asp with vbscript - it that correct ? I have tested it and it works fine. Do you have vb - if so test it with vb. How are you connecting to the database - post your code leading up to the recordset. Which version of sql server are you using - which version of ado/asp are you using ? Can you just set one of the field values equal to amount ?|||Hi rnealejr,
Thanks for the reply. Yes, you are right, I am using ASP with VBscript.

Version: SQL server 2000 & ASP3.0[IIS5.0].

Connection String: "Provider=SQLOLEDB;User ID=sa;password=xxx;Initial Catalog=myDb;Data Source=(local);"

set rs=server.createobject("ADODB.Recordset")
sql="Select * from myTable"
rs.open sql,con
if not rs.EOF then
Amt=rs("Rate")+rs("Interest") 'error in this line
end if

Can you just set one of the field values equal to amount ?
What do you mean by this? I didn't get you.

More Info: I am looking for a solution that is related with SQL server, rather than ASP. [because if the solution is ASP related, then I may have to change a lot of forms - around 150]

Please help me|||I will look into your code. Can you do the following:

Amt = rs("Rate")

and/or

Amt = rs("Interest")|||Hi,
Thanks again for your reply!
When I used Amt = rs("Rate"), it assigns the value.

money data-type query

I have a database table with a field of datatype "money".
I need to have this populated with rows that only have data up to a maximum
of two decimal places (i.e. pounds and pence).
However, I have come across some rows that have up to 3 significant figures
after the decimal points (i.e. 1/10th of a penny).
I need to identify all the rows that have more than two significant numbers
after the decimal place.
I thought:
SELECT * FROM myTable where
CAST(myField AS VARCHAR(20)) LIKE '%.[0-9][0-9][1-9]%'
would do the trick.
It doesn't.
Any suggestions would be really appreciated!
Thanks
GriffSorted:
WHERE (myField<> round(myField,2))
Griff|||"Griff" <Howling@.The.Moon> wrote in message
news:%23KcVFIIEGHA.140@.TK2MSFTNGP12.phx.gbl...
>I have a database table with a field of datatype "money".
> I need to have this populated with rows that only have data up to a
> maximum of two decimal places (i.e. pounds and pence).
> However, I have come across some rows that have up to 3 significant
> figures after the decimal points (i.e. 1/10th of a penny).
> I need to identify all the rows that have more than two significant
> numbers after the decimal place.
> I thought:
> SELECT * FROM myTable where
> CAST(myField AS VARCHAR(20)) LIKE '%.[0-9][0-9][1-9]%'
> would do the trick.
> It doesn't.
> Any suggestions would be really appreciated!
> Thanks
> Griff
>
Try this:
SELECT x
FROM tbl
WHERE x<>ROUND(x,2,1);
Be very careful when using MONEY for monetary amounts. In my opinion the
rounding errors caused by MONEY make DECIMAL a much better choice for
currency values in every case. See the example below. Do you have a good
excuse for using MONEY?
DECLARE
@.mon1 MONEY,
@.mon2 MONEY,
@.mon3 MONEY,
@.mon4 MONEY,
@.num1 DECIMAL(19,4),
@.num2 DECIMAL(19,4),
@.num3 DECIMAL(19,4),
@.num4 DECIMAL(19,4) ;
SELECT
@.mon1 = 100, @.mon2 = 339, @.mon3 = 10000,
@.num1 = 100, @.num2 = 339, @.num3 = 10000 ;
SET @.mon4 = @.mon1/@.mon2*@.mon3 ;
SET @.num4 = @.num1/@.num2*@.num3 ;
SELECT @.mon4 AS money_result,
@.num4 AS decimal_result ;
Result:
money_result decimal_result
-- --
2949.0000 2949.8525
(1 row(s) affected)
David Portas
SQL Server MVP
--|||The money data type stores data upto 4 decimal places. If you only require 2
decimal places you could consider storing the data as decimal or numeric
instead
HTH. Ryan
"Griff" <Howling@.The.Moon> wrote in message
news:OjWSPMIEGHA.344@.TK2MSFTNGP11.phx.gbl...
> Sorted:
> WHERE (myField<> round(myField,2))
> Griff
>

money data type

I choose a price field as a money data type, but I cannot change the
scale 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.3344If you want the scale to be 2, instead of using MONEY you might want to
consider using DECIMAL or NUMERIC datatype with the required precision &
scale.

--
Anith|||HandersonVA wrote:
> I choose a price field as a money data type, but I cannot change the
> scale 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

Use the DECIMAL(<precision>,<scale>) instead of the MONEY data type. E.g.:

sales_price DECIMAL(9,2)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Also the money data type is not a good candidate since there are some
rounding issues with it. Numeric/decimal is the way to go

Denis the SQL Menace
http://sqlservercode.blogspot.com/

HandersonVA wrote:
> I choose a price field as a money data type, but I cannot change the
> scale 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

Friday, March 30, 2012

More Newbie Questions

Wednesday, March 28, 2012

more empty fields

Monday, March 26, 2012

More Bytes returned from Image Data Type

Friday, March 23, 2012

Month value in SQL

Month Format

Month and date wrong way around

Friday, March 9, 2012

Monitor table changes without triggers.

Wednesday, March 7, 2012

monitor large update

Monday, February 20, 2012

Money to Varchar

Money to Varchar

money in stored procedure

money format ..need some help..please

money format

money format

Money field

Money datatype Substring

Money datatype Substring

Money datatype- simple question!

money data-type query

money data type

Money Data Type

Blog Archive

About Me