DECIMAL DATATYPE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DECIMAL DATATYPE

Author

Topic

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-06 : 16:53:36

Hey Gang,

I am trying to display in my table a value such as 70.15. The datatype I have chosen is DECIMAL. Only thing is, when this data is sent to this table as 70.15, it is displayed as 70.

I am sending the value from an .ASP page through a procedure in SQL SERVER.

Thank You for your time.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-06 : 17:46:03

sorry, I'm not clear on the question?
Are you writing this value to the database or are you selecting this value out of the database?
What is the datatype of the database column that holds this value?
When you say, "it is displayed as 70." where is it being displayed like that?

Be One with the Optimizer
TG

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-06 : 17:53:30

I apologize on not being so clear.

The value 70.15 is being written to the database.
This value is saved in a DECIMAL datatype field.
When I physically open the table to look at the value, it is saved as 70 and not 70.15

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-11-06 : 18:34:41

Are you selecting the data using Query Analyzer or Enterprise Manager? Don't assume EM displays data properly, quite often it doesn't.

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-06 : 19:05:41

Hi Rob,

I used the Query Analyzer also and I do get a decimal format (70.0) but, again I am not seeing the 70.15.

pootle_flump

1064 Posts

Posted - 2006-11-06 : 19:06:20

You also need to check how you defined your column.

CREATE TABLE DecT
	(
	Dec_a DECIMAL(4, 0)
	, Dec_b DECIMAL(4, 2)
	)

INSERT INTO DecT (Dec_a, Dec_b)
SELECT	70.15
	, 70.15

SELECT	*
FROM	DecT

DROP TABLE DecT
-------------------------
Dec_a,Dec_b
70,70.15

(1 row(s) affected)

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-06 : 19:25:07

The Precision is 6 and the Scale is 2.

I am not getting any numeric values on the right side of the decimal. The only numbers I see are 00. So instead of seeing 70.15, I am seeing 70.00 when I execute a Select statement in the Query Analyzer.

pootle_flump

1064 Posts

Posted - 2006-11-06 : 19:30:23

Is the parameter correctly defined in the procedure (same precision and scale)?

If so, perhaps you might post DDL of the table and the sproc code?

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-06 : 19:34:07

Here is the parameter being created in ASP
Set param2 = Comm2.CreateParameter("@Lbs",adDecimal,adParamInput,9,Lbs)
param2.Precision = 6
param2.NumericScale = 2
param2.Value = Lbs

Here is the Stored Procedure in SQL Server:

CREATE PROCEDURE NewFish(@Fish varchar (100),@Lbs decimal(9),@FishId numeric(9))
as
Insert into Fish(Fish,Lbs,FishId)
Values(@Fish,@Lbs,@FishId)
GO

pootle_flump

1064 Posts

Posted - 2006-11-06 : 19:41:40

If you change your sproc to:

CREATE PROCEDURE NewFish

	@Fish varchar (100)
	, @Lbs decimal(9)
	, @FishId numeric(9)

as

-- Insert into Fish(Fish,Lbs,FishId)
-- Values(@Fish,@Lbs,@FishId)

SELECT	@Lbs
GO

and exec the below in QA:

exec dbo.NewFish @Fish = 'Any Old Text', @Lbs = 70.15, @FishId = 1

then vwa-la:


70

(1 row(s) affected)

you need

 @Lbs decimal(6, 2)

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-06 : 19:46:49

You say I need this @Lbs decimal(6, 2).......Where do I need this?

pootle_flump

1064 Posts

Posted - 2006-11-06 : 19:50:33

In your sproc declaration instead of @Lbs decimal(9). Also (as an aside) Numeric is the same data type as Decimal and it is an unusual data type for a surrogate key (assuming fish_id is a surrogate).

HTH

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-06 : 19:53:28

Thats what I thought you meant.....still not working.

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-06 : 19:56:26

Is there a function I should wrap the numeric value in before I send it to the table via the procedure?

The value I had sent was 7015....trying to get a 70.15 format
I also concatenated 70 & "." & 15...trying to get the 70.15 format and
I tried wrapping 7015 in CInt/ABS.....trying to get the 70.15 format

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 23:52:24

Do not use integer division!

Try 7015 / 100.0 instead.

Peter Larsson
Helsingborg, Sweden

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-07 : 00:22:33

Problem Solved!!!!!! You get the Trophy Peso. Thank You and everyone else!!

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-07 : 00:43:25

SPOKE TO SOON......Now when I divide 7010 by 100.0 I get 70.1 when I want 70.10.
In the database it reads 70.10 but, is displayed on my HTML document 70.1.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 01:12:58

Still using decimal(6,2) ?
You are aware that 7010 / 100.0 is 70.1?
If you want more decimals displayed, use the FORMAT function on ASP page to display the trailing zeros.

Peter Larsson
Helsingborg, Sweden

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-07 : 10:25:12

Now we celebrate with drink. CHEERS PESO!!!!!!! The Function FORMATNUMBER in ASP did the job.

ASPSQLVB
Starting Member

46 Posts

Posted - 2006-11-07 : 11:05:00

Maybe I should of asked this a while ago but, how should Lbs and Oz be saved to the database and displayed on the interface?........The scale in the database for the DECIMAL datatype is set to 2. When a value like 70.1 comes in , it reads 70.10.......I would like to display 70.1.
Seems like I cannot get both 70.10 and 70.1. Its either one or the other.
If I use FormatNumber in ASP, it works for the value that is 70.10 but not for 70.1.
FormatNumber(RS1("Lbs"),2) this will display 70.10 instead of just showing 70.1
and if I use FormatNumber(RS1("Lbs"),1) this will display 70.1 instead of just showing 70.10.

Do I have to write a function detecting if there is just a 1 to the right of the decimal ?
Hmmmmm......
This data is in Lbs and Oz. So, I need to show 1 oz as well as 10 oz.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 11:11:09

You could use this in the ASP page
vFormat = FormatNumber(Rs1("Lbs"), 2)

If Right(vFormat, 1) = "0" Then vFormat = Left(vFormat, Len(vFormat) - 1)

Response.Write vFormat

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources