Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle SQL, round greater than 'half' only

Tags:

sql

oracle

I need to round 'half down' for a number and keep the (.5), for example:

if number = (9115.7 ) then (9116)

if number = (9115.5 ) then (9115.5)

if number = (9115.3 ) then (9115)

like image 407
soha mahmoud Avatar asked Aug 24 '19 10:08

soha mahmoud


People also ask

What is the difference between Ceil and floor in Oracle?

The FLOOR function determines the largest integer less than (or equal to) a particular numeric value. Conversely, the CEIL function determines the smallest integer greater than (or equal to) a particular numeric value.


1 Answers

You could rewrite own rounding logic using case expression:

CREATE TABLE t
AS
SELECT 9115.7 AS col FROM dual UNION ALL
SELECT 9115.5 AS col FROM dual UNION ALL
SELECT 9115.3 AS col FROM dual;

SELECT col, CASE WHEN MOD(ABS(col),1) = 0.5 THEN col ELSE ROUND(col) END
FROM t;

db<>fiddle demo

Output:

+---------+---------+
|  COL    | ROUNDED |
+---------+---------+
| 9115.7  |    9116 |
| 9115.5  |  9115.5 |
| 9115.3  |    9115 |
+---------+---------+
like image 86
Lukasz Szozda Avatar answered Sep 22 '22 06:09

Lukasz Szozda