I have a field on my report that is getting a number back from the supporting
stored proc. This number is sometimes a whole number, sometimes a decimal
number.
I want the number output to have 2 decimal places, no matter what the input.
I have tried the conditional formatting on the textbox, but it doesn't seem
to work. Is there a bug? Or am I doing something wrong?
I have input into the "format" section of the properties on the textbox:
#####.##
#,##
d2
D2
2D
2d
d#.##
d#,##
and many variations of the supposed formatting that is supposed to work.
Any suggestions?
Thanks!Hi,
Use an expression like FormatNumber(data,2).
Thanks|||The formatcode property of textboxes uses .NET format codes as defined on
MSDN. For numeric values check these links:
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
In your case, it sounds like you want formatcode N2
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"david boardman" <davidboardman@.discussions.microsoft.com> wrote in message
news:8A5FB147-B81B-40AB-9929-091F141358AD@.microsoft.com...
>I have a field on my report that is getting a number back from the
>supporting
> stored proc. This number is sometimes a whole number, sometimes a decimal
> number.
> I want the number output to have 2 decimal places, no matter what the
> input.
> I have tried the conditional formatting on the textbox, but it doesn't
> seem
> to work. Is there a bug? Or am I doing something wrong?
> I have input into the "format" section of the properties on the textbox:
> #####.##
> #,##
> d2
> D2
> 2D
> 2d
> d#.##
> d#,##
> and many variations of the supposed formatting that is supposed to work.
> Any suggestions?
> Thanks!|||Try N2
"david boardman" wrote:
> I have a field on my report that is getting a number back from the supporting
> stored proc. This number is sometimes a whole number, sometimes a decimal
> number.
> I want the number output to have 2 decimal places, no matter what the input.
> I have tried the conditional formatting on the textbox, but it doesn't seem
> to work. Is there a bug? Or am I doing something wrong?
> I have input into the "format" section of the properties on the textbox:
> #####.##
> #,##
> d2
> D2
> 2D
> 2d
> d#.##
> d#,##
> and many variations of the supposed formatting that is supposed to work.
> Any suggestions?
> Thanks!
Showing posts with label precision. Show all posts
Showing posts with label precision. Show all posts
Wednesday, March 7, 2012
Decimal field is rounding up my numbers
Hi,
I have a decimal field in SQL Server 2000 which has a precision value of 3 and scale 1. I will be storing values ranging from 0.5 to 10.0 in there. However, in my asp.net web form, if I select the value 2.5 from the DropDownList, SQL Server stores it as 3.
Can anyone tell me why this is happening and give me some pointers on what I can do to fix it? Your help is much appreciated.
how are you passing the values from the asp.net page? whats the datatype? can you post the asp.net code? also if you manually insert the values from query analyzer does it insert the right values?
|||Hi,
I entered 2.5 using QueryAnalyzer and SQL Server stored it as 2. So the issue is with the way I defined this Decimal field in SQL Server. The problem is I don't see where I'm making a mistake. If I set the precision to 3 and scale to 1, I should be able to have values ranging from 0.5 to 10.0 in there, should I?
|||your scale is right. try increasing your precision to 5. also check if you are working with the right column.|||Nope... I enter 3.5, it stores it as 4.0. And I'm doing this Query Analyzer so there's no question about where the data is going.|||
SamU wrote:
Nope... I enter 3.5, it stores it as 4.0. And I'm doing this Query Analyzer so there's no question about where the data is going.
I have added a testDecimal column to my Test table, and set it up as a decimal field with a precision of 3 and a scale of 1, and I am not seeing this odd behavior. It is storing the data exactly as I supply it (3.5, .5, 10.0, etc.) Please explain how exactly you are "doing this in Query Analyzer" as QA does not give you the facility to directly update the data; you need to execute a query to do this. I used queries like this:
UPDATE test SET testDecimal = 3.5 WHERE ID = 4For reference, when I first added the testDecimal column through Enterprise Manager, the default length was 9, the precision was 18, and the scale was 0. I changed the precision to 3 and the scale to 1, and the length automatically changed to 5.|||
SELECT testDecimal FROM test WHERE testDecimal IS NOT NULL
We may be narrowing this down. I'm actually inserting new values into a table and I'm doing this through a stored procedure. The parameter that inserts the value is defined as decimal. I'm including the code down below. Do I need any additional values that further define the parameter's precision and scale in the stored procedure? Looks like it's the stored procedure that's rounding the number up, not the table. Can anyone see an issue w/ this stored procedure?
Here's the Stored Procedure code. The parameter that inserts value into this field is @.JobLength.
ALTER PROCEDURE dbo.spTalentReleaseNew
(
@.EmployeeID smallint,
@.JobName varchar(200),
@.JobDate smalldatetime,
@.DealID int,
@.JobLength decimal,
@.TalentAgencyName varchar(200) = null,
@.TalentID int,
@.TalentRate smallmoney,
@.LocationRate smallmoney,
@.MakeUpRate smallmoney,
@.FoodStylistRate smallmoney,
@.LastUpdateTimeStamp datetime
)
AS
/* ObjectID = 221; This stored procedure creates a new Talent Release. */
INSERT INTO tblTalentRelease
(EmployeeID, JobName, JobDate, DealID,JobLength, TalentAgencyName, TalentID, TalentRate, LocationRate, MakeUpRate, FoodStylistRate,
LastUpdateTimeStamp, LastUpdatedBy)
VALUES (@.EmployeeID, @.JobName, @.JobDate, @.DealID,@.JobLength, @.TalentAgencyName, @.TalentID, @.TalentRate, @.LocationRate, @.MakeUpRate,
@.FoodStylistRate, @.LastUpdateTimeStamp, @.EmployeeID)
@.JobLength decimal(3, 1)
and you should be fine.
Don|||THanks Don.
decimal division precision
in my understanding of fixed numeric types and datatype precision rules,
dividing two decimals with identical precision/scale should result in a
decimal outcome with the same precision/scale. however, i run the following
in query analyzer:
declare @.price decimal(38,10)
declare @.mult decimal(38,10)
set @.price = 5.2347551174
set @.mult = 0.01
select @.price / @.mult
RESULT> 523.475511
if i convert @.mult to a float this all works as expected. whats going on her
e?
many thanks
kh> in my understanding of fixed numeric types and datatype precision rules,
> dividing two decimals with identical precision/scale should result in a
> decimal outcome with the same precision/scale
Precision, Scale, and Length
http://msdn.microsoft.com/library/d...br />
8rc5.asp
Try using a lower precision.
declare @.price decimal(18,10)
declare @.mult decimal(18,10)
set @.price = 5.2347551174
set @.mult = 0.01
select @.price / @.mult
go
Here is a very interesting script, from Steve Kass, to see the p and s of
the result.
http://www.microsoft.com/technet/co...>
6&sloc=en-us
AMB
"kh" wrote:
> in my understanding of fixed numeric types and datatype precision rules,
> dividing two decimals with identical precision/scale should result in a
> decimal outcome with the same precision/scale. however, i run the followin
g
> in query analyzer:
> declare @.price decimal(38,10)
> declare @.mult decimal(38,10)
> set @.price = 5.2347551174
> set @.mult = 0.01
> select @.price / @.mult
> RESULT> 523.475511
> if i convert @.mult to a float this all works as expected. whats going on h
ere?
> many thanks
> kh
>|||lovely. thanks.
kh
"Alejandro Mesa" wrote:
> Precision, Scale, and Length
> http://msdn.microsoft.com/library/d... />
b_8rc5.asp
> Try using a lower precision.
> declare @.price decimal(18,10)
> declare @.mult decimal(18,10)
> set @.price = 5.2347551174
> set @.mult = 0.01
> select @.price / @.mult
> go
> Here is a very interesting script, from Steve Kass, to see the p and s of
> the result.
> http://www.microsoft.com/technet/co...
c46&sloc=en-us
>
> AMB
>
> "kh" wrote:
>|||Another way to make it work, without changing the precision of your base
numbers...
Specifically cast your result, either in your SQL statement, or assign it to
a variable which has the proper precision.
declare @.price decimal(38,10)
declare @.mult decimal(38,10)
declare @.Result decimal(38,10)
set @.price = 5.2347551174
set @.mult = 0.01
set @.Result = @.price / @.mult
select @.price / @.mult
, cast(@.price / @.mult as decimal(38,10))
, @.Result
go
"kh" <kh@.newsgroups.nospam> wrote in message
news:92EE84D6-BEB4-419A-9054-76EF39B69608@.microsoft.com...
> lovely. thanks.
> kh
> "Alejandro Mesa" wrote:
>
rules,
a
http://msdn.microsoft.com/library/d..._da-db_8rc5.asp[
color=darkred]
of
http://www.microsoft.com/technet/co...5c46&sloc=en-us[color=darkr
ed]
rules,
a
following
on here?|||Jim,
Don't understand, the least significant digits are still truncated and
stuffed with 0s in your solution.
Anyways. Try this and check out the results. Is there a pattern that I am
not able to keep my finger on... there are only 6 places of decimal. or
rather ... I dunno.
P.S: Jim, Chance for u to fuel your ego :)
declare @.price decimal(38,10)
declare @.mult1 decimal(38,10)
declare @.mult2 decimal(38,10)
declare @.mult3 decimal(38,10)
set @.price = 5.2347551174
set @.mult1 = 0.01
set @.mult2 = 0.001
set @.mult3 = 0.0001
select @.price / @.mult1,
@.price / @.mult2,
@.price / @.mult3|||So they are...
Not sure what I was thinking when I looked at that yesterday. I would have
sworn the digits were there, but clearly they are not.
I guess the moral of the story is not simply casting everything when you do
math in SQL Server, but insuring that you only use the precision you
actually need.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:651C58FC-11D3-480D-A185-DDC6CDB1E5DF@.microsoft.com...
> Jim,
> Don't understand, the least significant digits are still truncated and
> stuffed with 0s in your solution.
> Anyways. Try this and check out the results. Is there a pattern that I am
> not able to keep my finger on... there are only 6 places of decimal. or
> rather ... I dunno.
> P.S: Jim, Chance for u to fuel your ego :)
> declare @.price decimal(38,10)
> declare @.mult1 decimal(38,10)
> declare @.mult2 decimal(38,10)
> declare @.mult3 decimal(38,10)
>
> set @.price = 5.2347551174
> set @.mult1 = 0.01
> set @.mult2 = 0.001
> set @.mult3 = 0.0001
>
> select @.price / @.mult1,
> @.price / @.mult2,
> @.price / @.mult3
>
dividing two decimals with identical precision/scale should result in a
decimal outcome with the same precision/scale. however, i run the following
in query analyzer:
declare @.price decimal(38,10)
declare @.mult decimal(38,10)
set @.price = 5.2347551174
set @.mult = 0.01
select @.price / @.mult
RESULT> 523.475511
if i convert @.mult to a float this all works as expected. whats going on her
e?
many thanks
kh> in my understanding of fixed numeric types and datatype precision rules,
> dividing two decimals with identical precision/scale should result in a
> decimal outcome with the same precision/scale
Precision, Scale, and Length
http://msdn.microsoft.com/library/d...br />
8rc5.asp
Try using a lower precision.
declare @.price decimal(18,10)
declare @.mult decimal(18,10)
set @.price = 5.2347551174
set @.mult = 0.01
select @.price / @.mult
go
Here is a very interesting script, from Steve Kass, to see the p and s of
the result.
http://www.microsoft.com/technet/co...>
6&sloc=en-us
AMB
"kh" wrote:
> in my understanding of fixed numeric types and datatype precision rules,
> dividing two decimals with identical precision/scale should result in a
> decimal outcome with the same precision/scale. however, i run the followin
g
> in query analyzer:
> declare @.price decimal(38,10)
> declare @.mult decimal(38,10)
> set @.price = 5.2347551174
> set @.mult = 0.01
> select @.price / @.mult
> RESULT> 523.475511
> if i convert @.mult to a float this all works as expected. whats going on h
ere?
> many thanks
> kh
>|||lovely. thanks.
kh
"Alejandro Mesa" wrote:
> Precision, Scale, and Length
> http://msdn.microsoft.com/library/d... />
b_8rc5.asp
> Try using a lower precision.
> declare @.price decimal(18,10)
> declare @.mult decimal(18,10)
> set @.price = 5.2347551174
> set @.mult = 0.01
> select @.price / @.mult
> go
> Here is a very interesting script, from Steve Kass, to see the p and s of
> the result.
> http://www.microsoft.com/technet/co...
c46&sloc=en-us
>
> AMB
>
> "kh" wrote:
>|||Another way to make it work, without changing the precision of your base
numbers...
Specifically cast your result, either in your SQL statement, or assign it to
a variable which has the proper precision.
declare @.price decimal(38,10)
declare @.mult decimal(38,10)
declare @.Result decimal(38,10)
set @.price = 5.2347551174
set @.mult = 0.01
set @.Result = @.price / @.mult
select @.price / @.mult
, cast(@.price / @.mult as decimal(38,10))
, @.Result
go
"kh" <kh@.newsgroups.nospam> wrote in message
news:92EE84D6-BEB4-419A-9054-76EF39B69608@.microsoft.com...
> lovely. thanks.
> kh
> "Alejandro Mesa" wrote:
>
rules,
a
http://msdn.microsoft.com/library/d..._da-db_8rc5.asp[
color=darkred]
of
http://www.microsoft.com/technet/co...5c46&sloc=en-us[color=darkr
ed]
rules,
a
following
on here?|||Jim,
Don't understand, the least significant digits are still truncated and
stuffed with 0s in your solution.
Anyways. Try this and check out the results. Is there a pattern that I am
not able to keep my finger on... there are only 6 places of decimal. or
rather ... I dunno.
P.S: Jim, Chance for u to fuel your ego :)
declare @.price decimal(38,10)
declare @.mult1 decimal(38,10)
declare @.mult2 decimal(38,10)
declare @.mult3 decimal(38,10)
set @.price = 5.2347551174
set @.mult1 = 0.01
set @.mult2 = 0.001
set @.mult3 = 0.0001
select @.price / @.mult1,
@.price / @.mult2,
@.price / @.mult3|||So they are...
Not sure what I was thinking when I looked at that yesterday. I would have
sworn the digits were there, but clearly they are not.
I guess the moral of the story is not simply casting everything when you do
math in SQL Server, but insuring that you only use the precision you
actually need.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:651C58FC-11D3-480D-A185-DDC6CDB1E5DF@.microsoft.com...
> Jim,
> Don't understand, the least significant digits are still truncated and
> stuffed with 0s in your solution.
> Anyways. Try this and check out the results. Is there a pattern that I am
> not able to keep my finger on... there are only 6 places of decimal. or
> rather ... I dunno.
> P.S: Jim, Chance for u to fuel your ego :)
> declare @.price decimal(38,10)
> declare @.mult1 decimal(38,10)
> declare @.mult2 decimal(38,10)
> declare @.mult3 decimal(38,10)
>
> set @.price = 5.2347551174
> set @.mult1 = 0.01
> set @.mult2 = 0.001
> set @.mult3 = 0.0001
>
> select @.price / @.mult1,
> @.price / @.mult2,
> @.price / @.mult3
>
Decimal DataType Question
What would I have to set the Decimal DataType's precision value to to accept:
5.5
0
1.5
3.5
etc...etc...?How large will the largest number be? Is there always at most a single decimal?
decimal(9,1) would work if you never have more than a single decimal palce. This would take up 5 bytes of storage, and nothing smaller would save you any space, so I would certainly leave it at that even if the number of digits to the left of the decimal (whole numbers) never exceeds 1.|||I've been experimenting and I think (4, 1) is going to work for me.
I just needed 0, 0.5, 1, 1.5, all the way up to 6.
Thanks!|||That is good, but keep in mind that even (4,1) is going to use the same 5 bytes of storage that (9,1) will. If there is ever any change of it getting larger, the extra space will not cost you anything.
Glad you got it going!
DECIMAL datatype
Can someone help me better understand the DECIMAL datatype? I've
defined a column as colname DEC(9,2). I know that precision is the
total number of digits, and scale is the number of digits to the right
of the decimal point. Yet 'sp_help tablename' indicates that colname
has a 'length' of 5. What does this 'length' refer to? Thanks.Storage bytes
Precision Storage bytes
1 - 9 ........5
10-19 .......9
20-28....... 13
29-38 .........17
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||i believe it's referred to byte.
"Rick Charnes" wrote:
> Can someone help me better understand the DECIMAL datatype? I've
> defined a column as colname DEC(9,2). I know that precision is the
> total number of digits, and scale is the number of digits to the right
> of the decimal point. Yet 'sp_help tablename' indicates that colname
> has a 'length' of 5. What does this 'length' refer to? Thanks.
>
defined a column as colname DEC(9,2). I know that precision is the
total number of digits, and scale is the number of digits to the right
of the decimal point. Yet 'sp_help tablename' indicates that colname
has a 'length' of 5. What does this 'length' refer to? Thanks.Storage bytes
Precision Storage bytes
1 - 9 ........5
10-19 .......9
20-28....... 13
29-38 .........17
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||i believe it's referred to byte.
"Rick Charnes" wrote:
> Can someone help me better understand the DECIMAL datatype? I've
> defined a column as colname DEC(9,2). I know that precision is the
> total number of digits, and scale is the number of digits to the right
> of the decimal point. Yet 'sp_help tablename' indicates that colname
> has a 'length' of 5. What does this 'length' refer to? Thanks.
>
Subscribe to:
Posts (Atom)