Post reply

Rounding numeric data

  • July 17, 2007 at 12:26 pm

    #177450

    Hi,

    I have the following record in my table.

    Cust#       Sal

    A            1000.00

    B            1.43838383838E+1

    C            -12.39393E-1

    How can I round only the last two records?

    help will be appreciated.

     

  • July 17, 2007 at 12:34 pm

    #720130

    what data type is you Sal column?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 17, 2007 at 12:42 pm

    #720133

    varchar(25)

    this data is coming to an import table. From there, it is validated and sent to the real table.

    In real table, the data type is money.

  • July 17, 2007 at 12:52 pm

    #720137

    Wouldn't that round just fine (to 4 digits precision) just be being copied into a money data type?  The negative number becomes -1.2394

  • July 17, 2007 at 12:54 pm

    #720138

    thanks for your mail. When I DTS it, it should become -1.20 OR -1.30 insread of being skipped.

  • July 18, 2007 at 7:51 am

    #720365

    Any scoop on this?

  • July 23, 2007 at 6:57 am

    #721509

    Do you mean that you want to round it to the nearest *tenth* instead of the nearest *ten-thousandth*?

  • July 23, 2007 at 9:20 pm

    #721735

    Yes/

  • July 23, 2007 at 11:31 pm

    #721761

    Ram,

    Dunno about the others, but I need to know why you want to simply throw away so many decimal places especially since the target datatype (Money) has 4 decimal places inherently available.  Is this, perchance, some form of duration, say, minutes that you want rounded to the nearest 1/10th of a minute?  If not, what is it?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 24, 2007 at 6:53 am

    #721876

    Thanks for your mail.

    I am gettign this data from a remote location where the users might have made the data entry incorrectly. But, still I have to validate this data.

     

    Thx,

    Ram

  • July 24, 2007 at 8:02 am

    #721921

    Ok... not the explanation I was looking for but I guess it'll do.

    If you want to round to a single decimal place, then convert it to to something like DECIMAL(19,1) and let the natural rounding take care of the rest.  First, you'll need to convert the char value to FLOAT (scientific notation won't convert to much else) and then convert to a single place decimal number... here's an oversimplified example...

    INSERT INTO yourtable (targetcolumn)

    SELECT CAST(CAST(sourcedata AS FLOAT) AS DECIMAL(19,1))

      FROM yourstagingtable

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 24, 2007 at 6:03 pm

    #722096

    Thanks for your time and it worked... Appreciate it..

    -- R

  • July 24, 2007 at 8:13 pm

    #722107

    You bet.  Thank you for the feedback, Ram.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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