Home » SQL & PL/SQL » SQL & PL/SQL » Rounding Decimal Numbers
Rounding Decimal Numbers Sat, 05 April 2008 00:35
 niva2004 Messages: 15Registered: 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
 Michel Cadot Messages: 65143Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Michel Cadot Messages: 65143Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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
 Frank Messages: 7880Registered: 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
 thomasscaria Messages: 21Registered: 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
 Frank Messages: 7880Registered: 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
 Michel Cadot Messages: 65143Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount 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: Want sql output in xls format Next Topic: replace the column value
Goto Forum:

Current Time: Sat Aug 19 08:08:07 CDT 2017

Total time taken to generate the page: 0.02559 seconds