#1
  1. I am still learning
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Kuala Lumpur, Malaysia
    Posts
    249
    Rep Power
    13

    Getting rids of unwanted decimals


    I am having problem to solve this simple problem of mine. Like what the subject said, I am trying to get rid of the trailing decimals for my output.

    What I want:

    input = 111.975
    output = 111

    What I don't want:

    input = 111.975
    output = 112

    I've tried:

    CAST(input AS INT)
    TO_CHAR(input,'99999999')

    Both fail to give me the output I want. How can I solve this decimal problem of mine?

    Any advice will be appreciated.

    Many thanks in advanced.

    Cheers.
    I have yet to find the right way to do it.

    www.genedavinci.com
  2. #2
  3. Second highest poster :p
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jul 2001
    Posts
    7,322
    Rep Power
    33
    You are thinking in the wrong way, treat it as a string not a number ;-)

    substring(fieldname from 1 for position('.' in fieldname))

    See how that works. I dont know how it will cope if there isnt a decimal.
    - Andreas Koepke

    Koepke Photography

  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    mexico
    Posts
    13
    Rep Power
    0
    This should also work:
    round( 111.795, 0 )
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    mexico
    Posts
    13
    Rep Power
    0
    I wrote round(111.795,0).
    It is more like trunc(111.795,0).
    Sorry about that.
  8. #5
  9. I am still learning
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Kuala Lumpur, Malaysia
    Posts
    249
    Rep Power
    13
    it works!

    however, what does '0' do in trunc(111.975,0)?

    i find that trunc(111.975) itself will work without the additional '0'.

    cheers.
    I have yet to find the right way to do it.

    www.genedavinci.com
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Location
    mexico
    Posts
    13
    Rep Power
    0
    There are two variants for trunc, one specifies the number of decimal places.

    trunc(111.795)=111
    trunc(111.795,0)=111
    trunc(111.795,2)=111.79

    You'll find this on the Postgresql 7.3.1 User's Guide ->Functions and Operators->Math Functions.
  12. #7
  13. I am still learning
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Kuala Lumpur, Malaysia
    Posts
    249
    Rep Power
    13
    thanks!
    I have yet to find the right way to do it.

    www.genedavinci.com

IMN logo majestic logo threadwatch logo seochat tools logo