Post reply

Format data

  • 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

  • October 31, 2002 at 4:58 pm

    #440007

    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

  • November 1, 2002 at 4:42 am

    #440031

    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.

  • November 1, 2002 at 10:52 am

    #440070

    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

  • November 1, 2002 at 3:07 pm

    #440102

    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?

  • November 1, 2002 at 4:34 pm

    #440123

    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

  • November 2, 2002 at 9:53 am

    #440144

    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.

  • November 3, 2002 at 3:36 am

    #440154

    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

  • November 3, 2002 at 9:11 pm

    #440169

    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

  • November 4, 2002 at 2:53 am

    #440176

    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

  • November 4, 2002 at 4:14 am

    #440186

    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.

  • November 4, 2002 at 7:31 am

    #440202

    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

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

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