Results 1 to 4 of 4

Thread: Rounding in SQL

  1. #1
    Join Date
    Jan 2004
    Location
    Somerset
    Posts
    5

    Unanswered: Rounding in SQL

    This may have been handled before in this forum...but as I am new here I would have missed it...here goes.

    I am trying to get an SQL statement to output numbers from an existing table.

    If I use round(field,2) I get the following:-

    Raw data............Rounded data

    0.00.........................0.0
    12.256..................12.26
    12.5.......................12.5
    12..........................12.0

    What I really want is everything to have 2 dec places IE 0.00 or 12.50

    I have tried TRUNC - same results.

    Is there a way to get 2 dec places regardless ?

    Nic

    Ps Thanks in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Rounding in SQL

    Originally posted by Nicjordan
    This may have been handled before in this forum...but as I am new here I would have missed it...here goes.

    I am trying to get an SQL statement to output numbers from an existing table.

    If I use round(field,2) I get the following:-

    Raw data............Rounded data

    0.00.........................0.0
    12.256..................12.26
    12.5.......................12.5
    12..........................12.0

    What I really want is everything to have 2 dec places IE 0.00 or 12.50

    I have tried TRUNC - same results.

    Is there a way to get 2 dec places regardless ?

    Nic

    Ps Thanks in advance.
    Hi,

    Please test defining DBFORMAT variable environment.

    Gustavo

  3. #3
    Join Date
    Jan 2004
    Location
    Somerset
    Posts
    5

    Re: Rounding in SQL

    Originally posted by gurey
    Hi,

    Please test defining DBFORMAT variable environment.

    Gustavo
    Sorry - I did not make myself clear - I dont want to change the data stored as it would effect a huge pre existing database - what I want to do is FORMAT the data I output in a SQL query - we dont have 4GL so I am limited to SQL.

    I am having trouble as the data is a floating point and sometimes there is only 1 or even 0 decimal places...but I want to show 2 - so I need the equivalent of rpad with 0 of r the decimal element.

    Nic

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

    Re: Rounding in SQL

    Originally posted by Nicjordan
    Sorry - I did not make myself clear - I dont want to change the data stored as it would effect a huge pre existing database - what I want to do is FORMAT the data I output in a SQL query - we dont have 4GL so I am limited to SQL.

    I am having trouble as the data is a floating point and sometimes there is only 1 or even 0 decimal places...but I want to show 2 - so I need the equivalent of rpad with 0 of r the decimal element.

    Nic
    Hi Nic,

    Excuse me, the environment variable DBFORMAT and DBMONEY, they permit you formatear the exit of the data. i understand that you want this.

    ie

    Value Format String DBFORMAT Setting Displayed Result
    1234.56 $$#,###.## $:,:.: $1,234.56
    1234.56 $$#,###.## :.:,M 1.234,56
    1234.56 #,###.##@@ $:,:.: 1,234.56
    1234.56 #,###.##@@ :.:,M 1.234,56DM

    Gustavo.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •