Home » SQL & PL/SQL » SQL & PL/SQL » Rounding Decimal Numbers
Rounding Decimal Numbers [message #311602] Sat, 05 April 2008 00:35 Go to next message
niva2004
Messages: 15
Registered: October 2006
Location: Cochin
Junior Member
Hi everybody


I have a problem in rounding decimal numbers.

I want to round the decimals as follows

5.12 to 5.10
5.14 to 5.10
5.16 to 5.15
5.19 to 5.15

Pls help me..its urgent
Re: Rounding Decimal Numbers [message #311604 is a reply to message #311602] Sat, 05 April 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
FLOOR or TRUNC depending on what you want for negative numbers.

Remember, nothing is urgent in forum unless you ask for no answer.

Regards
Michel
Re: Rounding Decimal Numbers [message #311607 is a reply to message #311602] Sat, 05 April 2008 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You posted this 3 times.

STOP THIS.

Regards
Michel
Re: Rounding Decimal Numbers [message #311611 is a reply to message #311607] Sat, 05 April 2008 00:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Make that FIVE times.
Re: Rounding Decimal Numbers [message #311613 is a reply to message #311602] Sat, 05 April 2008 01:38 Go to previous messageGo to next message
thomasscaria
Messages: 21
Registered: July 2007
Location: Kuwait
Junior Member
try this function. This code rounds the third number of the decimal part. You can modify for your purpose

FUNCTION ROUND_OFF (bill_amt NUMBER) RETURN NUMBER IS
 new_no number(10,3);
 dec_no number(10,3);
 last_digit number(1);
begin
 dec_no := bill_amt - substr(bill_amt,1,instr(bill_amt,'.') - 1);
 last_digit := substr(dec_no,4,1); 
  if last_digit between 1 and 5 and last_digit <> 5 then
   new_no := bill_amt + ((5 - last_digit) * 0.001); 
  elsif last_digit between 6 and 9 and last_digit <> 0 then
   new_no := bill_amt + ((10 - last_digit) * 0.001);
  else
   new_no := bill_amt;
  end if;
 return(new_no);
end;

Re: Rounding Decimal Numbers [message #311614 is a reply to message #311613] Sat, 05 April 2008 01:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
And what does your function do that you cannot do with a trunc(x, y) and some arithmetic ?
Apart from using string-functions on numbers, that is.
Just an example (I'm not good at math-tricks, so probably will be another solution)
  1  with data as (select (500 + level) / 100 as x from dual connect by level < 20)
  2* select x, trunc(2 * x, 1) / 2 from data
SQL> /

         X TRUNC(2*X,1)/2
---------- --------------
      5.01              5
      5.02              5
      5.03              5
      5.04              5
      5.05           5.05
      5.06           5.05
      5.07           5.05
      5.08           5.05
      5.09           5.05
       5.1            5.1
      5.11            5.1
      5.12            5.1
      5.13            5.1
      5.14            5.1
      5.15           5.15
      5.16           5.15
      5.17           5.15
      5.18           5.15
      5.19           5.15

[Updated on: Sat, 05 April 2008 01:53]

Report message to a moderator

Re: Rounding Decimal Numbers [message #311624 is a reply to message #311602] Sat, 05 April 2008 03:14 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have your answer why do you post this a SIXTH time?

Regards
Michel

[Updated on: Sat, 05 April 2008 03:15]

Report message to a moderator

Previous Topic: sql script error output
Next Topic: TRIGGER For Updating row if exists and insert new row if it not exists
Goto Forum:
  


Current Time: Sat Dec 10 01:27:23 CST 2016

Total time taken to generate the page: 0.08618 seconds