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 = 4
SELECT testDecimal FROM test WHERE testDecimal IS NOT NULL

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

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)

|||That's the problem. If you just declare the @.JobLength as decimal, it is created with a scale equal to zero as the default (and precision of 18). Set it like this instead:

@.JobLength decimal(3, 1)

and you should be fine.

Don|||THanks Don.