Decimal Data Type losing scale?

I'm trying to update a table that has decimal values. They are defined at
precision of 15 and scale of 2.
When I use a T-Sql update query, I'm sending a value "with pennies", but the
table is only reflecting the integer portion. I've even tried
UPDATE (myTableName)
SET myCost = CAST(@.Cost AS decimal(15,2))
without success.
When I step through the code of my VB.NET program and view the value of item
I've added to the parameters collection of my update query command object, I
DO SEE the pennies. When the query has executed, they aren't in the table.
I CAN TYPE the pennies into the record in the table with Enterprise Mgr. An
d
I can retrieve them with my program. But I can't send new values with
pennies and get them respected in the new table values.
I have seen in Books on Line that we are supposed to explicitly CAST our
decimal values. But shouldn't this take care of it?Hello Q,
When you run profiler what do you see the values being sent as? If you have
your precision and scale not matching exactly in your VB.NET application
it can cause it to send it incorrectly to the database.
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I'm trying to update a table that has decimal values. They are
> defined at precision of 15 and scale of 2.
> When I use a T-Sql update query, I'm sending a value "with pennies",
> but the
> table is only reflecting the integer portion. I've even tried
> UPDATE (myTableName)
> SET myCost = CAST(@.Cost AS decimal(15,2))
> without success.
> When I step through the code of my VB.NET program and view the value
> of item I've added to the parameters collection of my update query
> command object, I DO SEE the pennies. When the query has executed,
> they aren't in the table. I CAN TYPE the pennies into the record in
> the table with Enterprise Mgr. And I can retrieve them with my
> program. But I can't send new values with pennies and get them
> respected in the new table values.
> I have seen in Books on Line that we are supposed to explicitly CAST
> our decimal values. But shouldn't this take care of it?
>|||Is it possible that you've failed to specify precision and scale
for your decimal parameter? The default precision and scale for
a decimal parameter is precision 18, scale 0. If this doesn't seem
to help, could you post the relevant VB.NET code dealing with
the parameter?
Steve Kass
Drew University
Q Johnson wrote:

>I'm trying to update a table that has decimal values. They are defined at
>precision of 15 and scale of 2.
>When I use a T-Sql update query, I'm sending a value "with pennies", but th
e
>table is only reflecting the integer portion. I've even tried
> UPDATE (myTableName)
> SET myCost = CAST(@.Cost AS decimal(15,2))
>without success.
>When I step through the code of my VB.NET program and view the value of ite
m
>I've added to the parameters collection of my update query command object,
I
>DO SEE the pennies. When the query has executed, they aren't in the table.
>I CAN TYPE the pennies into the record in the table with Enterprise Mgr. A
nd
>I can retrieve them with my program. But I can't send new values with
>pennies and get them respected in the new table values.
>I have seen in Books on Line that we are supposed to explicitly CAST our
>decimal values. But shouldn't this take care of it?
>
>

Decimal data type

If I create a column with data type Decimal(10,4), when a data is being
stored in that column, will it format the data to be Decimal(10,4) ?
For example: if I enter 1.5, will it be 1.5000, or it will stay as 1.5 ?
When I tried it, it stays at 1.5.
Thanks.fniles wrote:

> If I create a column with data type Decimal(10,4), when a data is
> being stored in that column, will it format the data to be
> Decimal(10,4) ? For example: if I enter 1.5, will it be 1.5000, or
> it will stay as 1.5 ? When I tried it, it stays at 1.5.
No it will always stay 1.5 because 1.5000 is just a string
representation of a number. You best handle that in your application.
HTH,
Stijn Verrept.|||"fniles" <fniles@.pfmail.com> wrote in message
news:umjqImN$FHA.2520@.TK2MSFTNGP15.phx.gbl...
> If I create a column with data type Decimal(10,4), when a data is being
> stored in that column, will it format the data to be Decimal(10,4) ?
> For example: if I enter 1.5, will it be 1.5000, or it will stay as 1.5 ?
> When I tried it, it stays at 1.5.
> Thanks.
I'm going to guess that you're looking at the data in Enterprise Manager.
Go to Query Analyser and Select from that table.
In my SQL Server, EM formats it as 1.5 but in QA, 1.500.|||numbers are not stored with a format. format is a matter of display -
e.g., in Query Analyzer look in Tools \ Options \ Connections - see if
Use regional settings is checked.
fniles wrote:
> If I create a column with data type Decimal(10,4), when a data is being
> stored in that column, will it format the data to be Decimal(10,4) ?
> For example: if I enter 1.5, will it be 1.5000, or it will stay as 1.5 ?
> When I tried it, it stays at 1.5.
> Thanks.
>|||"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:e3yclwN$FHA.4028@.tk2msftngp13.phx.gbl...
> "fniles" <fniles@.pfmail.com> wrote in message
> news:umjqImN$FHA.2520@.TK2MSFTNGP15.phx.gbl...
> I'm going to guess that you're looking at the data in Enterprise Manager.
> Go to Query Analyser and Select from that table.
> In my SQL Server, EM formats it as 1.5 but in QA, 1.500.
Add an extra 0 to that output.
One rule to remember, SQL does not FORMAT data in tables, it stores data.
It will make your life simpler if you remember that rule.

Decimal column multiplication is rounding

On SQL2000, I'm joining 4 tables and multiplying four columns of DECIMAL (19,12) and my result gets rounded around the 5th place of scale. I've tried CASTing, changing sizes on the column(s) and I still seem to get the rounding.
byCalculator SumF
-- ---
2.6948768256 2.694877
I "SET NUMERIC_ROUNDABORT ON" and I get "Arithmetic overflow error converting numeric to data type numeric."
What am I missing?
I've listed sample tables, data inserts and just some of the selects that I tried that show the issue MUCH better than my words.
Any and all help is appreciated.
Creates:
CREATE TABLE [dbo].[factorA] ([factorAID] [int] IDENTITY (1, 1) NOT NULL ,[factorA_amt] [decimal](38, 12) NULL ON [PRIMARY]
GO
CREATE TABLE [dbo].[factorB] ([factorBID] [int] IDENTITY (1, 1) NOT NULL ,[factorB_amt] [decimal](38, 12) NULL ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[factorC] ([factorCID] [int] IDENTITY (1, 1) NOT NULL ,[factorC_amt] [decimal](38, 12) NULL ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[factorD] ([factorDID] [int] IDENTITY (1, 1) NOT NULL ,[factorD_amt] [decimal](38, 12) NULL ) ON [PRIMARY]
GO
Inserts:
insert into dbo.factorA (factorA_amt) VALUES (1.88)
GO
insert into dbo.factorB (factorB_amt) VALUES (1.11)
GO
insert into dbo.factorC (factorC_amt) VALUES (1.152)
GO
insert into dbo.factorD (factorD_amt) VALUES (1.121)
GO
Selects:
SELECT
2.6948768256 AS byCalculator,
(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS DECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(
CAST(a.factora_amt AS DECIMAL (38,12)) *
CAST(b.factorb_amt AS DECIMAL (38,12)) *
CAST(c.factorc_amt AS DECIMAL (38,12)) *
CAST(d.factord_amt AS DECIMAL (38,12))) AS DECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
FWIW,
I changed the precision to a total of 15 (keeping my scale at 12) and my calculations come out correct. I guess that the arithmetic of the table shown in BOL (and below) really needs to be thought through (although I saw a post here stating that it was s
lightly incorrect).
Sorry for the bandwidth waste!
Operation Result precision Result scale *
e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 * e2 p1 + p2 + 1 s1 + s2
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1)
* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated

Decimal column multiplication is rounding

On SQL2000, I'm joining 4 tables and multiplying four columns of DECIMAL (19
,12) and my result gets rounded around the 5th place of scale. I've tried C
ASTing, changing sizes on the column(s) and I still seem to get the rounding
.
byCalculator SumF
-- ---
2.6948768256 2.694877
I "SET NUMERIC_ROUNDABORT ON" and I get "Arithmetic overflow error convertin
g numeric to data type numeric."
What am I missing?
I've listed sample tables, data inserts and just some of the selects that I
tried that show the issue MUCH better than my words.
Any and all help is appreciated.
Creates:
CREATE TABLE [dbo].[factorA] ( [factorAID] [int] IDENTITY (1
, 1) NOT NULL , [factorA_amt] [decimal](38, 12) NULL ON [PRIMAR
Y]
GO
CREATE TABLE [dbo].[factorB] ( [factorBID] [int] IDENTITY (1
, 1) NOT NULL , [factorB_amt] [decimal](38, 12) NULL ) ON [PRIMA
RY]
GO
CREATE TABLE [dbo].[factorC] ( [factorCID] [int] IDENTITY (1
, 1) NOT NULL , [factorC_amt] [decimal](38, 12) NULL ) ON [PRIMA
RY]
GO
CREATE TABLE [dbo].[factorD] ( [factorDID] [int] IDENTITY (1
, 1) NOT NULL , [factorD_amt] [decimal](38, 12) NULL ) ON [PRIMA
RY]
GO
Inserts:
insert into dbo.factorA (factorA_amt) VALUES (1.88)
GO
insert into dbo.factorB (factorB_amt) VALUES (1.11)
GO
insert into dbo.factorC (factorC_amt) VALUES (1.152)
GO
insert into dbo.factorD (factorD_amt) VALUES (1.121)
GO
Selects:
SELECT
2.6948768256 AS byCalculator,
(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(a.factora_amt * b.factorb_amt * c.factorc_amt * d.factord_amt) AS D
ECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GO
SELECT
2.6948768256 AS byCalculator,
CAST(SUM(
CAST(a.factora_amt AS DECIMAL (38,12)) *
CAST(b.factorb_amt AS DECIMAL (38,12)) *
CAST(c.factorc_amt AS DECIMAL (38,12)) *
CAST(d.factord_amt AS DECIMAL (38,12))) AS DECIMAL (38,24)) AS SumF
FROM FACTORA a
INNER JOIN FACTORB b ON a.factoraID = b.factorbID
INNER JOIN FACTORC c ON a.factoraID = c.factorcID
INNER JOIN FACTORD d ON a.factoraID = d.factordID
GOFWIW,
I changed the precision to a total of 15 (keeping my scale at 12) and my cal
culations come out correct. I guess that the arithmetic of the table shown
in BOL (and below) really needs to be thought through (although I saw a post
here stating that it was s
lightly incorrect).
Sorry for the bandwidth waste!
Operation Result precision Resu
lt scale *
e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2)
e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2)
e1 * e2 p1 + p2 + 1 s1
+ s2
e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 +
p2 + 1)
* The result precision and scale have an absolute maximum of 38. When a resu
lt precision is greater than 38, the corresponding scale is reduced to preve
nt the integral part of a result from being truncated

Decimal being rounded in Insert statement

Hi - when I insert a decimal amount into my SQL Server 2000 database, it
rounds it up.
My table has a column:
customer_amount decimal 9
..and has Precision set at 18, and Scale set at 2.
I am grabbing the decimal value from a textbox on my form, and passing
it as a parameter in ASP.Net:
cmd.Parameters.Add(New SqlParameter("@.customer_amount",
CType(tbConfAmount.Text, Decimal)))
My SP is:
CREATE Procedure addCustAmount
(
@.customer_amount decimal
)
AS INSERT INTO tblCustomerAmount (customer_amount)
VALUES
(@.customer_amount)
If my text box says: 23.79, the value that ends up in my database is 24.
Can anyone please advise where I'm going wrong?
Thanks, Mark
*** Sent via Developersdex http://www.examnotes.net ***Mark
select cast (23.79 as decimal)
select cast (23.79 as decimal(5,2))
"Mark" <anonymous@.devdex.com> wrote in message
news:%23mCgVUpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi - when I insert a decimal amount into my SQL Server 2000 database, it
> rounds it up.
> My table has a column:
> customer_amount decimal 9
> ..and has Precision set at 18, and Scale set at 2.
> I am grabbing the decimal value from a textbox on my form, and passing
> it as a parameter in ASP.Net:
> cmd.Parameters.Add(New SqlParameter("@.customer_amount",
> CType(tbConfAmount.Text, Decimal)))
> My SP is:
> CREATE Procedure addCustAmount
> (
> @.customer_amount decimal
> )
> AS INSERT INTO tblCustomerAmount (customer_amount)
> VALUES
> (@.customer_amount)
> If my text box says: 23.79, the value that ends up in my database is 24.
> Can anyone please advise where I'm going wrong?
> Thanks, Mark
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You don't specify a Scale for the parameter for your stored procedure, so
that defaults to 0. Decimal without Precision or Scale is decimal(18,0) by
default. Just change the datatype of the parameter to decimal(18,2). You
might have to do that in the parameter definition in ASP.Net as well btw,
but my ADO.Net is a bit rusty.
Jacco Schalkwijk
SQL Server MVP
"Mark" <anonymous@.devdex.com> wrote in message
news:%23mCgVUpPFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi - when I insert a decimal amount into my SQL Server 2000 database, it
> rounds it up.
> My table has a column:
> customer_amount decimal 9
> ..and has Precision set at 18, and Scale set at 2.
> I am grabbing the decimal value from a textbox on my form, and passing
> it as a parameter in ASP.Net:
> cmd.Parameters.Add(New SqlParameter("@.customer_amount",
> CType(tbConfAmount.Text, Decimal)))
> My SP is:
> CREATE Procedure addCustAmount
> (
> @.customer_amount decimal
> )
> AS INSERT INTO tblCustomerAmount (customer_amount)
> VALUES
> (@.customer_amount)
> If my text box says: 23.79, the value that ends up in my database is 24.
> Can anyone please advise where I'm going wrong?
> Thanks, Mark
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

Decimal and Number Formatting

I have a written a function where I am defining the return value as decimal. Now I need to do the formatting
to make the negative number look like (123.34%) and postive numbers as 123.34%. When I try to do this, I am
getting values like (123.3456788). How do I get rid of these extra decimals?
** Format(Fields!<FieldName>.Value,"#,###,##0.00;(#,###,##0.00)") is not working since I am using Sum for aggregation.
Thanks a lot for your help.Go to the Textbox Properties dialog in designer and specify
"#,##0.00%;(#,##0.00%);Zero" (without the double quotes) as the custom
format string.
Ravi Mumulla
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Taps" <Taps@.discussions.microsoft.com> wrote in message
news:89671ED3-88A9-4FE3-B1FA-109DCDE50065@.microsoft.com...
> I have a written a function where I am defining the return value as
decimal. Now I need to do the formatting
> to make the negative number look like (123.34%) and postive numbers as
123.34%. When I try to do this, I am
> getting values like (123.3456788). How do I get rid of these extra
decimals?
> ** Format(Fields!<FieldName>.Value,"#,###,##0.00;(#,###,##0.00)") is not
working since I am using Sum for aggregation.
> Thanks a lot for your help.
>|||It works! Thanks a lot.
"Ravi Mumulla (Microsoft)" wrote:
> Go to the Textbox Properties dialog in designer and specify
> "#,##0.00%;(#,##0.00%);Zero" (without the double quotes) as the custom
> format string.
> Ravi Mumulla
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Taps" <Taps@.discussions.microsoft.com> wrote in message
> news:89671ED3-88A9-4FE3-B1FA-109DCDE50065@.microsoft.com...
> > I have a written a function where I am defining the return value as
> decimal. Now I need to do the formatting
> > to make the negative number look like (123.34%) and postive numbers as
> 123.34%. When I try to do this, I am
> > getting values like (123.3456788). How do I get rid of these extra
> decimals?
> >
> > ** Format(Fields!<FieldName>.Value,"#,###,##0.00;(#,###,##0.00)") is not
> working since I am using Sum for aggregation.
> >
> > Thanks a lot for your help.
> >
> >
>
>|||Taps wrote:
> I have a written a function where I am defining the return value as
> decimal. Now I need to do the formatting to make the negative number
> look like (123.34%) and postive numbers as 123.34%. When I try to do
> this, I am getting values like (123.3456788). How do I get rid of
> these extra decimals?
> ** Format(Fields!<FieldName>.Value,"#,###,##0.00;(#,###,##0.00)") is
> not working since I am using Sum for aggregation.
> Thanks a lot for your help.
Try setting format property at cell level (right click at cell or cells
then y properties set format)

decimal

Dear guys,
I have a data like 28.56 I want only 56, how can I get..
I can get 28 after converting in to Int datatype , what about 56, how can I
get , any buildin function available.
Thanks
NOOR
On Mon, 2 Aug 2004 23:41:31 -0700, Noor wrote:

>Dear guys,
>I have a data like 28.56 I want only 56, how can I get..
>I can get 28 after converting in to Int datatype , what about 56, how can I
>get , any buildin function available.
>Thanks
>NOOR
Hi Noor,
Do you want 0.56 or 56?
Several options are in the script below.
declare @.a decimal(4,2)
set @.a = 28.56
select @.a
select cast(@.a as int)
select @.a - cast(@.a as int)
select (@.a - cast(@.a as int)) * 100
select substring(cast(@.a - cast(@.a as int) as varchar),3,2)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo
Noor
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:6jpug0h5eoa24b5urkg8sm8hdgnmtm6v5r@.4ax.com... [vbcol=seagreen]
> On Mon, 2 Aug 2004 23:41:31 -0700, Noor wrote:
I
> Hi Noor,
> Do you want 0.56 or 56?
> Several options are in the script below.
> declare @.a decimal(4,2)
> set @.a = 28.56
> select @.a
> select cast(@.a as int)
> select @.a - cast(@.a as int)
> select (@.a - cast(@.a as int)) * 100
> select substring(cast(@.a - cast(@.a as int) as varchar),3,2)
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)