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.
Showing posts with label decimal. Show all posts
Showing posts with label decimal. Show all posts
Monday, February 20, 2012
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
>
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 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/
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
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]
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:
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
>>
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]
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]
Subscribe to:
Posts (Atom)