More Newbie Questions

Two questions --

Why is it that some of my tables are not updateable, and some are??

And I'm told that there is no "memo" type field for SQLServer, and 8000
characters is the limit. Unless I go to blob or binary. What does that
entail, and what are the consequences?

Thanks!

JA> Why is it that some of my tables are not updateable, and some are??

There could be more than one reason depending on how you are doing the
updating and what you mean by "not updateable". I'll take a guess that you
are using a UI such as Enterprise Manager or Access that allows you to edit
a table. In that case you won't be able to make changes unless your table
has a unique key (constraint or index). Every table should always have a
unique key otherwise it isn't possible to guarantee that individual rows can
be updated. The solution is to add a primary key constraint to the table.

If I'm wrong then please explain how you are attempting to update the table
and what happens when you try (do you get an error message for example).

> And I'm told that there is no "memo" type field for SQLServer, and 8000
> characters is the limit. Unless I go to blob or binary. What does that
> entail, and what are the consequences?

VARCHAR columns are limited to 8000 characters. For text up to 2GB in size
you can use the TEXT or NTEXT datatype. TEXT isn't as easy to use in your
SQL code as VARCHAR is and there are some limitations on what you can and
cannot do - for example some of the string functions won't work with TEXT
values. Lookup the TEXT datatype in Books Online.

--
David Portas
SQL Server MVP
--|||David,

You're right, I'm using Access 2000. I made a table with a primary key and
had no problem adding data.

I looked up TEXT and NTEXT on Books Online. I don't guess I know enough to
see the problems. The field is for product descriptions that are over 8000
characters (quite a few are). They have HTML tags in them, and are displayed
on an asp webpage. Would there be any problems with that?

Thanks, JA

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:tMSdne7GYNyvpQXfRVn-1w@.giganews.com...
>> Why is it that some of my tables are not updateable, and some are??
> There could be more than one reason depending on how you are doing the
> updating and what you mean by "not updateable". I'll take a guess that
> you are using a UI such as Enterprise Manager or Access that allows you to
> edit a table. In that case you won't be able to make changes unless your
> table has a unique key (constraint or index). Every table should always
> have a unique key otherwise it isn't possible to guarantee that individual
> rows can be updated. The solution is to add a primary key constraint to
> the table.
> If I'm wrong then please explain how you are attempting to update the
> table and what happens when you try (do you get an error message for
> example).
>> And I'm told that there is no "memo" type field for SQLServer, and 8000
>> characters is the limit. Unless I go to blob or binary. What does that
>> entail, and what are the consequences?
> VARCHAR columns are limited to 8000 characters. For text up to 2GB in size
> you can use the TEXT or NTEXT datatype. TEXT isn't as easy to use in your
> SQL code as VARCHAR is and there are some limitations on what you can and
> cannot do - for example some of the string functions won't work with TEXT
> values. Lookup the TEXT datatype in Books Online.
> --
> David Portas
> SQL Server MVP
> --

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:
>

Money type limitation

Does anyone know if Microsoft is planning to expand sql server Money
data type to larger what it is in sql server 2000; that is larger than
922,337,203,685,477.5807?
Thanks
HilalI haven't heard of such plans. You can always request at sqlwish@.microsoft.com. Or use NUMERIC
instead.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hilal Issa" <hilal@.edm.com.lb> wrote in message news:u9ak8h9tDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Does anyone know if Microsoft is planning to expand sql server Money
> data type to larger what it is in sql server 2000; that is larger than
> 922,337,203,685,477.5807?
> Thanks
> Hilal
>|||I don't know about future versions but if you want values larger than that
today use DECIMAL/NUMERIC which handles up to 38 digits preceision.
--
David Portas
--
Please reply only to the newsgroup
--
"Hilal Issa" <hilal@.edm.com.lb> wrote in message
news:u9ak8h9tDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Does anyone know if Microsoft is planning to expand sql server Money
> data type to larger what it is in sql server 2000; that is larger than
> 922,337,203,685,477.5807?
> Thanks
> Hilal
>|||Thanks for the replies.
I will send an e-mail to sqlwish@.microsoft.com as Tibor suggested.
In fact, money data type would enough except in some cases where some values
might go beyond its boundaries; ex.: I have a table with 3 fields Qty, Unit
Amount and SubTotal (=Qty * Unit Amount). If we set Qty = 9999999 (seven
9's) and Unit Price = 99999999 (eight 9's)and Now calculate SubTotal = Qty *
Unit Amount, an overflow error is generated.
Indeed, I was trying converting all my fields using money datatype to
Decimal (38, 5) that is allowing 5 decimal places. I did not finish testing
with it yet. But I am guessing from now that I will have this problem with
wider range. Float (up to 1.79E + 308) would have been great but its
floating issue is not good at all in my case.
Best Regards
Hilal
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:SPidnZLuAOq5aleiRVn-hg@.giganews.com...
> I don't know about future versions but if you want values larger than that
> today use DECIMAL/NUMERIC which handles up to 38 digits preceision.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "Hilal Issa" <hilal@.edm.com.lb> wrote in message
> news:u9ak8h9tDHA.2340@.TK2MSFTNGP12.phx.gbl...
> > Does anyone know if Microsoft is planning to expand sql server Money
> > data type to larger what it is in sql server 2000; that is larger than
> > 922,337,203,685,477.5807?
> >
> > Thanks
> >
> > Hilal
> >
> >
>|||A bigger range for a money datatype is rediculous. Even the biggest
budget of the biggest country will not exceed 900 trillion.
I would suggest you simply handle any overflows. Who is going to spend
900 trillion dollars in your shop by buying 10 million units that cost
100 million dollars each? That just doesn't make sense.
My 5 cents,
Gert-Jan
Hilal Issa wrote:
> Thanks for the replies.
> I will send an e-mail to sqlwish@.microsoft.com as Tibor suggested.
> In fact, money data type would enough except in some cases where some values
> might go beyond its boundaries; ex.: I have a table with 3 fields Qty, Unit
> Amount and SubTotal (=Qty * Unit Amount). If we set Qty = 9999999 (seven
> 9's) and Unit Price = 99999999 (eight 9's)and Now calculate SubTotal = Qty *
> Unit Amount, an overflow error is generated.
> Indeed, I was trying converting all my fields using money datatype to
> Decimal (38, 5) that is allowing 5 decimal places. I did not finish testing
> with it yet. But I am guessing from now that I will have this problem with
> wider range. Float (up to 1.79E + 308) would have been great but its
> floating issue is not good at all in my case.
> Best Regards
> Hilal
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:SPidnZLuAOq5aleiRVn-hg@.giganews.com...
> > I don't know about future versions but if you want values larger than that
> > today use DECIMAL/NUMERIC which handles up to 38 digits preceision.
> >
> > --
> > David Portas
> > --
> > Please reply only to the newsgroup
> > --
> >
> > "Hilal Issa" <hilal@.edm.com.lb> wrote in message
> > news:u9ak8h9tDHA.2340@.TK2MSFTNGP12.phx.gbl...
> > > Does anyone know if Microsoft is planning to expand sql server Money
> > > data type to larger what it is in sql server 2000; that is larger than
> > > 922,337,203,685,477.5807?
> > >
> > > Thanks
> > >
> > > Hilal
> > >
> > >
> >
> >|||On Tue, 02 Dec 2003 23:49:37 +0100, Gert-Jan Strik
<sorry@.toomuchspamalready.nl> wrote:
>A bigger range for a money datatype is rediculous. Even the biggest
>budget of the biggest country will not exceed 900 trillion.
>I would suggest you simply handle any overflows. Who is going to spend
>900 trillion dollars in your shop by buying 10 million units that cost
>100 million dollars each? That just doesn't make sense.
Whilst in principle I agree with you, consider applications that must
handle foreign currencies. In an application I worked on recently for a
bank, I was somewhat surprised when a summary report returned figures in
the trillions. As it happened, it was due to my not converting foreign
currencies to local currency (AUD), but be aware that there are
situations where trillions of currency units are quite within realistic
bounds.
cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander

Money type confusion

I need a little advice for the Money datatype. When entering a value like $18.20 into a column of datatype Money, it stores and returns a value of 18.2 . It is set to the default paramaters. How can I change this so that it is correctly displayed as $18.20. My results are printed onscreen through VB, is that where I need to reformat?You sure it's money?

DECLARE @.x money

SELECT @.x = 18.20

SELECT @.x

EDIT: It's a presentation layer issue...

Then There Also

SELECT CONVERT(varchar(15),@.x,1)|||Positive it's Money|||Where's the sql being executed from? a stored procedure or called from a front end?

Did you try the code I posted in QA?|||It's a .asp page with VB. It's a dynamic SQL statment. And after running your code I got 18.2000. So it looks like it's good to 4 decimal places. This may be dumb to ask now, but should I kill that column and recreate it?|||You should be able to use the Convert function to format your output as a string, but formatting is best handled by your application interface.

blindman|||Do you know how to script the table so we can see the DDL?

Did you use the convert from the page?|||I'm a graphic designer doing the basics on MSSQL, I didn't even know you could script a table let alone tell you what a DDL is. But I'm a smart guy, I'll catch on quick.

And by converting do you mean something like CSTR to cast the returned value into a string or is it done within the SQL statment?

Originally posted by Brett Kaiser
Do you know how to script the table so we can see the DDL?

Did you use the convert from the page?|||SELECT CONVERT(varchar(15),@.x,1)

Just replace the @.x with the column name

Do you have SQL Server client tools installed on your desktop?

Do you know what Enterprise Manager is?|||Actually you should make the varchar(25) to handle all possible money values...

money and smallmoney
money

Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

smallmoney

Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.|||[i]Actually you should make the varchar(25) to handle all possible money values...

quote:
------------------------

money and smallmoney
money

Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

smallmoney

Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.

------------------------

Brett ...
That sounds like an awful lot of Money. Hope the person having so much can lend some to me :D|||HAHA, after 5 minutes of staring at the wrong webpage I was working on, I got myself straightened out and found the right one. The Convert expression works like magic.

And yeah, i'm working in Enterprise Manager. I guess I'm a little above basic.|||I thought 15 was enough...but you still have to take in the right side of the decimal, the decimal itself, commas, and a sign...25

922 Trillion Dollars...|||Thanks for you help, I should be able to manage from here|||If we were dealing with 922 Trillion Dollars do you think I'd be posting to a free forum for help? I think they'd have someone a little more experienced on this.|||Actually, it's more a matter of a development methodolgy...always code for what can happen...always...

That way there will never be any holes...|||Originally posted by stmaher
If we were dealing with 922 Trillion Dollars do you think I'd be posting to a free forum for help? I think they'd have someone a little more experienced on this.

There go my hopes down the drain :D.

BTW Brett is right. The code should take care of all possible conditions.

Brett ... though I think varchar(15) will take care of Bill Gates wealth for sure|||Free forum !? What is your mailing address ? Or you can call the toll free number and submit your credit card information.|||What? Have NONE of you guys been receiving your checks?

Must be a problem in the payroll database...

blindman|||Just send me your SSN's and mailing addresses and I will get you on the payroll. If you receive any unexpected mail, like credit cards ..., ignore it and just forward them to me. :-)|||> That sounds like an awful lot of Money

hang on a sec...you must be assuming USD. I wonder whether the amount would still look so large if it was to refer to Japanese Yen. Not to mention some inflated currency from a developing country.|||You'd be surprized.
We have a thing in the IT dept. we call the "2 comma rule".

In other words, if the number (currency) doesn't have at least 2 commas, it isn't worth worrying about.

Swear to God.

Money Data Type Question

Hi All,
I have a few columns that are of a type Money.
My problem is that the data has four decimal places to the right all the
time e.g...
1032.0000
.0000
12.1200
100.100
etc...
How can I set the columns of type Money to only provide two decimal places.
I would think that this would be automatic or what's the difference between
Money and Decimal?
Thanks very much,
John.John Rugo wrote:
> How can I set the columns of type Money to only provide two decimal places
.
> I would think that this would be automatic or what's the difference betwee
n
> Money and Decimal?
money and smallmoney are specialized forms of decimal. As such, the
definition of money states that it has a scale of 4.
If you only want it to have a scale of 2 then you will need to use a
decimal and define it as such.
For more information look at Books Online for decimal.
http://msdn.microsoft.com/library/e..._de-dz_3grn.asp
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/

Money data type format

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?No, thats the standard format. If you want 2 decimals, you need to use Decimal datatype with precision set to 2.

Money data type - no bigmoney :(

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?
Use DECIMAL data type instead. It's an exact number, like MONEY is. It's limited to a total of 38 digits, but you can configure the scale up to 38 digits if you like.

Eg: DECIMAL(10,6) would allow you to have a number like 1234.123456
DECIMAL(22,8) would allow you to have a number like 12345678912345.12345678

From BOL: Money Data Type, Overview: If a greater number of decimal places are required, use the decimal data type instead.

Money data type

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.

regardsUse string.format("{0:c}", yourvalue)

This will format it in the currency used by your web server.

If you want more control, use "$" & string.format("{0:#,##0.00}", yourvalue)|||Hi PDraigh

I think u may have goven me VB code, actually I was using C#..

string.format("{0:#,##0.00}", yourvalue)

In the above code, what does 'string' represent|||I assume it would still work. 'string' is just the System.String. It's not a variable or object I declared. "{0:#,##0.00}" is the string formatting instruction and 'yourvalue' is just whatever value you want to format using the instruction. Try it, I think it would work in C#, but I don't use C#, so not sure|||yep

String and Format should be capital, such as String.Format("{0:#,##0.00}", price);

However, I still get 2.0000 istead of 2.00|||try,
Label2.Text = String.Format("{0:$##,##0.00}",dataSet1.Tables["products"].Rows[0]["price"]);

or

Label2.Text = String.Format("{0:c}",dataSet1.Tables["products"].Rows[0]["price"]);

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

Money Data Type

Recently any query run that calls for a filed of the money data type has started displaying all four decimal places. The only thing that appears to have changed is the hotfix referenced in MS04-16 (Deals with Direct X) was applied to the server. As far
as I can tell the money data type has always had a scale of 4. Does anyone know why our queries are showing all four decimal places now? I guess more importantly does anyone know how to make it return to only displaying 2 decimal places? I already sugg
ested casting the values to decmial data type with a scale of 2, but our developers rejected that idea as there are thousands of stored procedures they would have to go through to do that.
Thanks in advance,
Jason
Jason,
Please differentiate between a value and the presentation of a value. SQL Server returns values (in
binary format) and the client application present those values. Obviously, the programmer of your
client applications didn't bother to define the presentation format and because of that has some
default setting for that dev environment, something which changed with the security fix. But SQL
Server does not and cannot present any data. The client application does. This is where you have to
go hunting.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Szotak" <Jason Szotak@.discussions.microsoft.com> wrote in message
news:E5816A4C-F985-486B-AF01-0718C80D095C@.microsoft.com...
> Recently any query run that calls for a filed of the money data type has started displaying all
four decimal places. The only thing that appears to have changed is the hotfix referenced in
MS04-16 (Deals with Direct X) was applied to the server. As far as I can tell the money data type
has always had a scale of 4. Does anyone know why our queries are showing all four decimal places
now? I guess more importantly does anyone know how to make it return to only displaying 2 decimal
places? I already suggested casting the values to decmial data type with a scale of 2, but our
developers rejected that idea as there are thousands of stored procedures they would have to go
through to do that.
> Thanks in advance,
> Jason
|||Tibor,
Thanks for the response. I could use a little more clarification though. When running a stored procedure through query analyer or in the display of a view through enterprise manager the output now shows the four decimal places. It used to show only tw
o. Is it possible that a patch somehow changed this? Like I said before, it started about a week ago and the only thing that has changed in that week is the one hotfix for Direct X was applied. One of our developers thinks that if the display of the va
lue returns to 2 decimal places when running a stored procedure through query analyzer that our problem would be solved. Would you consider this to be an accurate statement?
"Tibor Karaszi" wrote:

> Jason,
> Please differentiate between a value and the presentation of a value. SQL Server returns values (in
> binary format) and the client application present those values. Obviously, the programmer of your
> client applications didn't bother to define the presentation format and because of that has some
> default setting for that dev environment, something which changed with the security fix. But SQL
> Server does not and cannot present any data. The client application does. This is where you have to
> go hunting.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
|||After invetigating this issue further I think the change to the system was that we upgraded the .Net Framework on our webserver to v1.1. The developer that is having the problem is using ASP.Net. We also have applications written in asp. When a stored
procedure is run in query analyzer on a money field the output has the four decimal places, but our asp web page only show the decimals places that are non-zero (the value 100.4000 would display as 100.4 and the value 100.0400 displays as 100.04), so like
you said our development environment has changed and probably has nothing to do with the hotfix. That said, Is there a way to configure the .Net Framework v1.1 to only show two decimal places for money data types?
Jason
"Tibor Karaszi" wrote:

> Jason,
> Please differentiate between a value and the presentation of a value. SQL Server returns values (in
> binary format) and the client application present those values. Obviously, the programmer of your
> client applications didn't bother to define the presentation format and because of that has some
> default setting for that dev environment, something which changed with the security fix. But SQL
> Server does not and cannot present any data. The client application does. This is where you have to
> go hunting.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
|||I don't know the framework enough to guess if or where you do that. I suggest you post that to a
..NET fx group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Szotak" <JasonSzotak@.discussions.microsoft.com> wrote in message
news:0F1A91C6-AA58-4DBB-AED9-8A2A463B774A@.microsoft.com...
> After invetigating this issue further I think the change to the system was that we upgraded the
..Net Framework on our webserver to v1.1. The developer that is having the problem is using ASP.Net.
We also have applications written in asp. When a stored procedure is run in query analyzer on a
money field the output has the four decimal places, but our asp web page only show the decimals
places that are non-zero (the value 100.4000 would display as 100.4 and the value 100.0400 displays
as 100.04), so like you said our development environment has changed and probably has nothing to do
with the hotfix. That said, Is there a way to configure the .Net Framework v1.1 to only show two
decimal places for money data types?[vbcol=seagreen]
> Jason
> "Tibor Karaszi" wrote:
(in[vbcol=seagreen]
your[vbcol=seagreen]
to[vbcol=seagreen]

money data type

is money data type can only store 4 decimal places?
what data type should I use if I want to store more than 4 decimal places?
I use SQL server 2000 edition
Did you look at the "decimal" datatype?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"kei" <kei@.discussions.microsoft.com> wrote in message
news:C359CF38-D84A-4B99-8158-55150E573249@.microsoft.com...
> is money data type can only store 4 decimal places?
> what data type should I use if I want to store more than 4 decimal places?
> I use SQL server 2000 edition
|||what is the different between decimal and money datatype? will decimal
datatype less accurate?
"Tibor Karaszi" wrote:

> Did you look at the "decimal" datatype?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:C359CF38-D84A-4B99-8158-55150E573249@.microsoft.com...
>
>
|||It depends on what precision and scale you specify. I suggest you start by reading about the decimal
datatype in Books Online and post back here if you after that have further questions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"kei" <kei@.discussions.microsoft.com> wrote in message
news:620A1CE5-B44A-45E8-81D5-2D881FAC9E0E@.microsoft.com...[vbcol=seagreen]
> what is the different between decimal and money datatype? will decimal
> datatype less accurate?
> "Tibor Karaszi" wrote:

money data type

is money data type can only store 4 decimal places?
what data type should I use if I want to store more than 4 decimal places?
I use SQL server 2000 editionDid you look at the "decimal" datatype?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"kei" <kei@.discussions.microsoft.com> wrote in message
news:C359CF38-D84A-4B99-8158-55150E573249@.microsoft.com...
> is money data type can only store 4 decimal places?
> what data type should I use if I want to store more than 4 decimal places?
> I use SQL server 2000 edition|||what is the different between decimal and money datatype? will decimal
datatype less accurate?
"Tibor Karaszi" wrote:
> Did you look at the "decimal" datatype?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:C359CF38-D84A-4B99-8158-55150E573249@.microsoft.com...
> > is money data type can only store 4 decimal places?
> > what data type should I use if I want to store more than 4 decimal places?
> >
> > I use SQL server 2000 edition
>
>|||It depends on what precision and scale you specify. I suggest you start by reading about the decimal
datatype in Books Online and post back here if you after that have further questions.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"kei" <kei@.discussions.microsoft.com> wrote in message
news:620A1CE5-B44A-45E8-81D5-2D881FAC9E0E@.microsoft.com...
> what is the different between decimal and money datatype? will decimal
> datatype less accurate?
> "Tibor Karaszi" wrote:
>> Did you look at the "decimal" datatype?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "kei" <kei@.discussions.microsoft.com> wrote in message
>> news:C359CF38-D84A-4B99-8158-55150E573249@.microsoft.com...
>> > is money data type can only store 4 decimal places?
>> > what data type should I use if I want to store more than 4 decimal places?
>> >
>> > I use SQL server 2000 edition
>>

Money Data Type

Recently any query run that calls for a filed of the money data type has sta
rted displaying all four decimal places. The only thing that appears to hav
e changed is the hotfix referenced in MS04-16 (Deals with Direct X) was appl
ied to the server. As far
as I can tell the money data type has always had a scale of 4. Does anyone
know why our queries are showing all four decimal places now? I guess more
importantly does anyone know how to make it return to only displaying 2 deci
mal places? I already sugg
ested casting the values to decmial data type with a scale of 2, but our dev
elopers rejected that idea as there are thousands of stored procedures they
would have to go through to do that.
Thanks in advance,
JasonJason,
Please differentiate between a value and the presentation of a value. SQL Se
rver returns values (in
binary format) and the client application present those values. Obviously, t
he programmer of your
client applications didn't bother to define the presentation format and beca
use of that has some
default setting for that dev environment, something which changed with the s
ecurity fix. But SQL
Server does not and cannot present any data. The client application does. Th
is is where you have to
go hunting.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Szotak" <Jason Szotak@.discussions.microsoft.com> wrote in message
news:E5816A4C-F985-486B-AF01-0718C80D095C@.microsoft.com...
> Recently any query run that calls for a filed of the money data type has started d
isplaying all
four decimal places. The only thing that appears to have changed is the hot
fix referenced in
MS04-16 (Deals with Direct X) was applied to the server. As far as I can te
ll the money data type
has always had a scale of 4. Does anyone know why our queries are showing a
ll four decimal places
now? I guess more importantly does anyone know how to make it return to onl
y displaying 2 decimal
places? I already suggested casting the values to decmial data type with a
scale of 2, but our
developers rejected that idea as there are thousands of stored procedures th
ey would have to go
through to do that.
> Thanks in advance,
> Jason|||Tibor,
Thanks for the response. I could use a little more clarification though. Wh
en running a stored procedure through query analyer or in the display of a v
iew through enterprise manager the output now shows the four decimal places.
It used to show only tw
o. Is it possible that a patch somehow changed this? Like I said before, i
t started about a week ago and the only thing that has changed in that week
is the one hotfix for Direct X was applied. One of our developers thinks th
at if the display of the va
lue returns to 2 decimal places when running a stored procedure through quer
y analyzer that our problem would be solved. Would you consider this to be
an accurate statement?
"Tibor Karaszi" wrote:

> Jason,
> Please differentiate between a value and the presentation of a value. SQL
Server returns values (in
> binary format) and the client application present those values. Obviously,
the programmer of your
> client applications didn't bother to define the presentation format and be
cause of that has some
> default setting for that dev environment, something which changed with the
security fix. But SQL
> Server does not and cannot present any data. The client application does.
This is where you have to
> go hunting.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/|||After invetigating this issue further I think the change to the system was t
hat we upgraded the .Net Framework on our webserver to v1.1. The developer
that is having the problem is using ASP.Net. We also have applications writ
ten in asp. When a stored
procedure is run in query analyzer on a money field the output has the four
decimal places, but our asp web page only show the decimals places that are
non-zero (the value 100.4000 would display as 100.4 and the value 100.0400 d
isplays as 100.04), so like
you said our development environment has changed and probably has nothing to
do with the hotfix. That said, Is there a way to configure the .Net Framew
ork v1.1 to only show two decimal places for money data types?
Jason
"Tibor Karaszi" wrote:

> Jason,
> Please differentiate between a value and the presentation of a value. SQL
Server returns values (in
> binary format) and the client application present those values. Obviously,
the programmer of your
> client applications didn't bother to define the presentation format and be
cause of that has some
> default setting for that dev environment, something which changed with the
security fix. But SQL
> Server does not and cannot present any data. The client application does.
This is where you have to
> go hunting.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/|||I don't know the framework enough to guess if or where you do that. I sugges
t you post that to a
.NET fx group.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Szotak" <JasonSzotak@.discussions.microsoft.com> wrote in message
news:0F1A91C6-AA58-4DBB-AED9-8A2A463B774A@.microsoft.com...
> After invetigating this issue further I think the change to the system was that we
upgraded the
.Net Framework on our webserver to v1.1. The developer that is having the
problem is using ASP.Net.
We also have applications written in asp. When a stored procedure is run in
query analyzer on a
money field the output has the four decimal places, but our asp web page onl
y show the decimals
places that are non-zero (the value 100.4000 would display as 100.4 and the
value 100.0400 displays
as 100.04), so like you said our development environment has changed and pro
bably has nothing to do
with the hotfix. That said, Is there a way to configure the .Net Framework
v1.1 to only show two
decimal places for money data types?[vbcol=seagreen]
> Jason
> "Tibor Karaszi" wrote:
>
(in[vbcol=seagreen]
your[vbcol=seagreen]
to[vbcol=seagreen]

Modulo Type Question

How do I get Modulo on:
select 200501 / 4
The sum = 50125.25
I want the .25
I tried:
select 200501 / 4
select CAST((200501 / 4) as float)
select CAST((200501 / 4) as real)
select CAST((200501 / 4) as numeric(16,3))
Also how can one use Modulo(%) with 200501 / 4 ?Modulo returns the remainder, which would be 1 in your case.
Try this. (When I ran your statements I only got a whole number without the
decimal portion.)
declare @.a float, @.b float
select @.a=200501, @.b=4
select @.a/@.b -floor(@.a/@.b)
"marcmc" wrote:

> How do I get Modulo on:
> select 200501 / 4
> The sum = 50125.25
> I want the .25
> I tried:
> select 200501 / 4
> select CAST((200501 / 4) as float)
> select CAST((200501 / 4) as real)
> select CAST((200501 / 4) as numeric(16,3))
> Also how can one use Modulo(%) with 200501 / 4 ?
>|||select (200501 % 4) / convert(money, 4)

> How do I get Modulo on:
> select 200501 / 4
> The sum = 50125.25
> I want the .25
> I tried:
> select 200501 / 4
> select CAST((200501 / 4) as float)
> select CAST((200501 / 4) as real)
> select CAST((200501 / 4) as numeric(16,3))
> Also how can one use Modulo(%) with 200501 / 4 ?
new|||modulo only operates on integer values and returns an integer which is
the remainder, so that's not really what you're looking for here.
the easiest way is to subtract the integer part from the result.
[make sure one of the values is a decimal since you've got two integers]
e.g.
select convert(numeric(16,2), (200501/4.0) - convert(int, (200501/4.0)))
are these for hard-coded numbers or variables/data, integers or numeric?
the actual code will be different depending
marcmc wrote:
> How do I get Modulo on:
> select 200501 / 4
> The sum = 50125.25
> I want the .25
> I tried:
> select 200501 / 4
> select CAST((200501 / 4) as float)
> select CAST((200501 / 4) as real)
> select CAST((200501 / 4) as numeric(16,3))
> Also how can one use Modulo(%) with 200501 / 4 ?
>