Decimal Precision - 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

Old Forums

CLOSED - General SQL Server

Decimal Precision

Author

Topic

l.lopez
Starting Member

18 Posts

Posted - 2004-07-06 : 11:28:16

We have had problem with the decimals precision within real values. For example, Any values are stored as shown below:

Value ***** Instead of
6.0999999 ---> 6.00
28.120001 ---> 28.12
0.91000003 ---> 1.00
9.0000004E-2 ---> 0.00.

What could be wrong ?

L.L.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 11:59:21

You need to store the values as DECIMAL(6,2) if you want to have it how your "Instead of" column is using it. You are now storing it as a float, which would be a real mess if you're doing accounting or something with it. Real in SQL Server is just a float.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

l.lopez
Starting Member

18 Posts

Posted - 2004-07-06 : 12:11:15

Ok, What could be happen if I change the type of field?... Are affected the values in the field?... We have 64 fields declared like real. What should We do?

L.L.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 14:03:26

Well, before you do that select the values out of them with

SELECT CAST(column AS DECIMAL(9,2))

for example and see if this is what really want to do. If so, then by all means have at it.


CREATE TABLE #columntest(
	itsanumberalright REAL)

INSERT #columntest(itsanumberalright)
	SELECT 14.12388011

SELECT * FROM #columntest

ALTER TABLE #columntest ALTER COLUMN itsanumberalright DECIMAL(9,2)

SELECT * FROM #columntest

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-06 : 14:36:52

Before you do ANYTHING...backup the database

Brett

8-)

l.lopez
Starting Member

18 Posts

Posted - 2004-07-06 : 14:39:17

I think this will help me too much. I go to try it.

Thanks a lot by the support

L.L.

l.lopez
Starting Member

18 Posts

Posted - 2004-07-06 : 15:47:19

By the way, the declaration DECIMAL(9,2) means 9 integer and 2 decimals or 7 integer and 2 decimals?. I ask this because in Oracle DB means 7 integer and 2 decimals, and I am not sure in SQL Server.

Thanks in advance

L.L.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-06 : 15:53:30

Do you have Query Analyzer open? It's kinda easy to test if you do...


DECLARE @x decimal(9,2)
SELECT @x = 123456789.1234
SELECT @x
GO
DECLARE @x decimal(9,2)
SELECT @x = 1234567.12
SELECT @x
GO

Brett

8-)

l.lopez
Starting Member

18 Posts

Posted - 2004-07-06 : 15:59:10

Yes, I have it. The First one is wrong, I just realized that works like Oracle.

Thanks again Brett.

L.L.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-06 : 16:18:34

But not like COBOL....


DECLARE @x decimal(9,2)
SELECT @x = 123456789.12
SELECT @x
GO

What about vb?

Brett

8-)

l.lopez
Starting Member

18 Posts

Posted - 2004-07-06 : 16:31:32

OK, In VB I used to declarate this kind of data like Single or Double. To be honest, I have never worked with COBOL.

L.L.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 17:12:13

Ahhh, so sad. (NOT) You aren't missing anything. :) Hey, hiring any DBA's in Venezuela? Are you in Caracas. I need a DBA position in Margarita. WOO HOO!!!!

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

l.lopez
Starting Member

18 Posts

Posted - 2004-07-06 : 17:44:14

In Our Country there is not enough hiring, It is difficult due to the political problems. I am from Maracaibo. Would you like to find a job in Margarita?

L.L.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 17:48:23

I would love to. I have a couple friends from Maracaibo. I'm also aware of the economical and political situation right now. Maybe some day though. It's a beautiful country with a lot of resources. I don't think your current political leaders would like me though. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-07 : 10:21:08

I think you guys want a job in Margaritaville....

But I doubt there's any dba work there....however if you know how to work a blender.....

or roll a spliff

you may be in bd'ness

Brett

8-)

l.lopez
Starting Member

18 Posts

Posted - 2004-07-07 : 15:13:13

Hey Brett, My english is not as good. What does it mean "to work a blender..... or roll a spliff"? and "bd'ness"?

This is too deeper for me...

Regards

L.L.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-07 : 19:05:23

Work a blender....get a job as a bartender or something making Margarita's, which is what they all drink in huge amounts. The others......don't worry, it's not your English. We don't know what he's saying half the time either. We just ignore him. You know....figure he's talking to his other self again.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

l.lopez
Starting Member

18 Posts

Posted - 2004-07-08 : 08:32:46

AAAhhh Ok, Brett is so funny. That's good. At least he enjoys his work. On the other hand, I tell you that today is my birthday. You should help me to change the 64 real fields to decimal. I just have changed like 10.

Regards

L.L.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-08 : 08:37:55

DROP DATABASE database_name

That should fix you up.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

l.lopez
Starting Member

18 Posts

Posted - 2004-07-08 : 08:43:56

You are so funny, too. To be honest, I think that It's the best thing I can do...

L.L.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-08 : 08:45:38

Oh, and Happy Birthday.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.