Format data

Question

Post reply

Format data

billmais

SSC Enthusiast

Points: 134
More actions
October 31, 2002 at 4:45 pm

#173569

Hi,
I have a number field that displays too many decimal places (i.e. 1.00000000001) how can I round off the data to display only 2 decimal places?
I have tried;
update tableA
set field1 = Round(field1, 2)
where field1 is not null
Thanks in advance,
Bill Mais

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

You can try something like this:

select cast(1111.0860009 as decimal(38,2))

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Greg is right, thr ROUND function only rounds the value out to that length but the output length of the data remains intact. Thus 1.00000000001 round out to a length of 2 looks at

1.000 and you round to 1.00 but the length is still 1.00000000001 so the output is 1.00000000000

better example to show this is

1.05487985 Initial Value

1.054 Part looked at by Length 2 in Round

1.05 Value whe rounded

1.05000000 Output due to initial length.

By casting to Decimanl it will automaitcally be rounded and the output length decimal length adjusted to your length paramter.

billmais SSC Enthusiast Points: 134 More actions · Answer 3

Thanks,

I asked my question incorrectly. What I would like to do is change the data in the database to only store 2 decimals (from 1.000000001 to 1.00). I do not have the luxury of changing the database, but I can control new data going in. Now I need to clean up existing data.

Thanks in advance,

Bill

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Then use the cast as Greg suggests in yuor update in the place of your Round.

Is there a specific reason you cannot change the DB to stored as DECIMAL with a 2 decimal position?

billmais SSC Enthusiast Points: 134 More actions · Answer 5

Hi,

I tried:

update tableA

set field1 = cast(field1 as decimal(38, 2))

where field1 is not null

and it did not work, the data is still stored 1.00000000001

To make a short story long, the reason I cannot change the database is that a program called Pivotal Relationship controls the structure of the database. Pivotal is much like (please don't flame me) Seibel, SalesLogix and Vantive where it is is a complete CRM or SFA system. You add, modify, or delete tables and fields only through Pivotal. It only "understands" a few data types. In its defense, Pivotal is a very powerful and flexible program, and more importantly, it is what we have.

Thanks,

Bill

Antares686 SSC Guru Points: 125444 More actions · Answer 6

What datatype is the field already? I am wondering if the current type is overriding the data somehow. Float I have heard of doing this because they are not precise.

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 7

Definitley sounds like the column is using a float rather than a decimal. Floats are imprecise and so holding decimal numbers will lead to rounding errors.

This is especially true when you multiply lots of them. We have a legacy app that is often out by 1p because of using imprecise floating points in VB

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

billmais SSC Enthusiast Points: 134 More actions · Answer 8

You guys are good!

The datafield is specified by Pivotal as currency, but in sql the datafield is a Float length=8 precision=15. If I use sql to change it to something else, Pivotal would change it back the next time it makes changes to the database. Pivotal works with the number as currency and writes to the db as currency (100.00). But we have some asp pages that did not. I am fixing the asp pages to format the numbers correctly. Now I need to go back and fix existing data to be stored as currency.

How can I reformat this existing data to store only 2 decimal places?

Thanks,

Bill

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 9

The point is that floating points will store decimals inaccurately. i.e if you put 0.03 in the database it will probably store it as 0.029999999. So you will never be able to ensure that if you put 0.03 in you will get 0.03 out

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Antares686 SSC Guru Points: 125444 More actions · Answer 10

Float goes out far enough that rounding will never be an issue as the rounding is only to the secound decimal place when you define a cast of decimal with a scale of 2. I would for all purpose just create a view that casts that column in it's output to a decimal or money, since you are talking currency. However, money outputs 4 decimal positions so you may prefer decimal if you only want 2.

billmais SSC Enthusiast Points: 134 More actions · Answer 11

Interesting, but my options are limited. I cannot change the structure of the table and I do not want to change much of the web pages. The numbers we are working with are large (100,000 to 2,000,000) so accuracy is not the big problem. Pivotal stores numbers correctly (###.00). But the web pages were writing numbers with too many decimal places (##.000000000001). The users were seeing numbers like 1,500,000.3333333 and telling us to loose the .33333333 part. What I would like to do is fix the existing data to only display 2 digits but my sql statement;

update tableA

set field1 = cast(field1 as decimal(38, 2))

where field1 is not null

did not work.

Is my statement wrong? Is there a better way to do this?

I really appreciate your looking into this and the discussions have been informative.

Thanks,

Bill