A common need when processing numbers is to adjust values up or down, such as rounding to nearest integer or removing the fractional part of a number. Oracle has a variety of means of doing so. Unfortunately sometimes developers will confuse functions that produce similar results under some circumstances only to be surprised when they get erroneous results in others. Most notably this occurs with positive and negative numbers.
By default, ROUND returns the nearest integer to the input value. This is the same rounding that most students learn in elementary school.
SQL> SELECT ROUND(4.7) FROM DUAL;
ROUND(4.7)
----------
5
SQL> SELECT ROUND(-4.7) FROM DUAL;
ROUND(-4.7)
-----------
-5
SQL> SELECT ROUND(4.3) FROM DUAL;
ROUND(4.3)
----------
4
SQL> SELECT ROUND(-4.3) FROM DUAL;
ROUND(-4.3)
-----------
-4
The next function, CEIL for “ceiling” is sometimes called rounding up. The next integer of equal or greater value is returned. Note, for negative numbers, the next value will have a smaller absolute value. As seen below, the ceiling of 4.7 rounds “up” to 5, but the ceiling of -4.7 is -4, not -5.
SQL> SELECT CEIL(4.7) FROM DUAL;
CEIL(4.7)
----------
5
SQL> SELECT CEIL(-4.7) FROM DUAL;
CEIL(-4.7)
----------
-4
SQL> SELECT CEIL(4.3) FROM DUAL;
CEIL(4.3)
----------
5
SQL> SELECT CEIL(-4.3) FROM DUAL;
CEIL(-4.3)
----------
-4
The FLOOR function is the counterpoint to CEIL, returning the next integer equal to or lower than the input value, or “rounding down.”
SQL> SELECT FLOOR(4.7) FROM DUAL;
FLOOR(4.7)
----------
4
SQL> SELECT FLOOR(-4.7) FROM DUAL;
FLOOR(-4.7)
-----------
-5
SQL> SELECT FLOOR(4.3) FROM DUAL;
FLOOR(4.3)
----------
4
SQL> SELECT FLOOR(-4.3) FROM DUAL;
FLOOR(-4.3)
-----------
-5
The last function TRUNC, cuts off the fractional part of a number, leaving only the integer portion. Thus always returning a value of equal or smaller absolute value.
SQL> SELECT TRUNC(4.7) FROM DUAL;
TRUNC(4.7)
----------
4
SQL> SELECT TRUNC(-4.7) FROM DUAL;
TRUNC(-4.7)
-----------
-4
SQL> SELECT TRUNC(4.3) FROM DUAL;
TRUNC(4.3)
----------
4
SQL> SELECT TRUNC(-4.3) FROM DUAL;
TRUNC(-4.3)
-----------
-4
ROUND and TRUNC also each support a second parameter to adjust for precision. That is, you can round to positions left and right of the decimal point.
SQL> select round(3.14159,1), round(3.14159,2), round(3.14159,3), round(3.14159,4) from dual;
ROUND(3.14159,1) ROUND(3.14159,2) ROUND(3.14159,3) ROUND(3.14159,4)
---------------- ---------------- ---------------- ----------------
3.1 3.14 3.142 3.1416
SQL> select trunc(3.14159,1), trunc(3.14159,2), trunc(3.14159,3), trunc(3.14159,4) from dual;
TRUNC(3.14159,1) TRUNC(3.14159,2) TRUNC(3.14159,3) TRUNC(3.14159,4)
---------------- ---------------- ---------------- ----------------
3.1 3.14 3.141 3.1415
Using negative values for the second parameter will round or truncate to the left of the decimal point.
SQL> select round(98765,-1), round(98765,-2), round(98765,-3), round(98765,-4) from dual;
ROUND(98765,-1) ROUND(98765,-2) ROUND(98765,-3) ROUND(98765,-4)
--------------- --------------- --------------- ---------------
98770 98800 99000 100000
SQL> select trunc(98765,-1), trunc(98765,-2), trunc(98765,-3), trunc(98765,-4) from dual;
TRUNC(98765,-1) TRUNC(98765,-2) TRUNC(98765,-3) TRUNC(98765,-4)
--------------- --------------- --------------- ---------------
98760 98700 98000 90000
ROUND and TRUNC are also overloaded to support DATE type. By default they round or truncate to the DAY, i.e. returning a DATE value with time of 00:00:00. The second parameter can be used to override the default using a subset of the substrings used for text formatting.
SQL> SELECT d, ROUND(d), TRUNC(d)
2 FROM (SELECT TO_DATE('2019-08-20 08:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL
3 UNION ALL
4 SELECT TO_DATE('2019-08-20 18:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL);
D ROUND(D) TRUNC(D)
------------------- ------------------- -------------------
2019-08-20 08:30:00 2019-08-20 00:00:00 2019-08-20 00:00:00
2019-08-20 18:30:00 2019-08-21 00:00:00 2019-08-20 00:00:00
SQL> SELECT t, ROUND(t), TRUNC(t)
2 FROM (SELECT TO_timestamp('2019-08-20 08:30', 'yyyy-mm-dd hh24:mi') t FROM DUAL
3 UNION ALL
4 SELECT TO_timestamp('2019-08-20 18:30', 'yyyy-mm-dd hh24:mi') t FROM DUAL);
T ROUND(T) TRUNC(T)
----------------------------- ------------------- -------------------
2019-08-20 08:30:00.000000000 2019-08-20 00:00:00 2019-08-20 00:00:00
2019-08-20 18:30:00.000000000 2019-08-21 00:00:00 2019-08-20 00:00:00
SQL> SELECT t, ROUND(t), TRUNC(t)
2 FROM (SELECT TO_TIMESTAMP_TZ('2019-08-20 08:30 US/Eastern', 'yyyy-mm-dd hh24:mi TZR') t FROM DUAL
3 UNION ALL
4 SELECT TO_TIMESTAMP_TZ('2019-08-20 18:30 US/Eastern', 'yyyy-mm-dd hh24:mi TZR') t FROM DUAL);
T ROUND(T) TRUNC(T)
------------------------------------ ------------------- -------------------
2019-08-20 08:30:00.000000000 -04:00 2019-08-20 00:00:00 2019-08-20 00:00:00
2019-08-20 18:30:00.000000000 -04:00 2019-08-21 00:00:00 2019-08-20 00:00:00
It bears repeating: the timestamp results above show both ROUND and TRUNC will produce a DATE result. There is no native method for rounding or truncating timestamps (with or without time zones) that will return the same output type as the input type. For this reason the remainder of the examples will only use the DATE type for input as timestamps would yield equivalent results.
SQL> --- Round/Trunc to the week
SQL> SELECT d, ROUND(d,'d'), TRUNC(d,'d')
2 FROM (SELECT TO_DATE('2019-08-20 08:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL
3 UNION ALL
4 SELECT TO_DATE('2019-08-22 18:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL);
D ROUND(D,'D') TRUNC(D,'D')
------------------- ------------------- -------------------
2019-08-20 08:30:00 2019-08-18 00:00:00 2019-08-18 00:00:00
2019-08-22 18:30:00 2019-08-25 00:00:00 2019-08-18 00:00:00
SQL> --- Round/Trunc to the year
SQL> SELECT d, ROUND(d,'y'), TRUNC(d,'y')
2 FROM (SELECT TO_DATE('2019-08-20 08:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL
3 UNION ALL
4 SELECT TO_DATE('2019-08-22 18:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL);
D ROUND(D,'Y') TRUNC(D,'Y')
------------------- ------------------- -------------------
2019-08-20 08:30:00 2020-01-01 00:00:00 2019-01-01 00:00:00
2019-08-22 18:30:00 2020-01-01 00:00:00 2019-01-01 00:00:00
SQL> --- Round/Trunc to the hour
SQL> SELECT d, ROUND(d,'hh'), TRUNC(d,'hh')
2 FROM (SELECT TO_DATE('2019-08-10 08:25', 'yyyy-mm-dd hh24:mi') d FROM DUAL
3 UNION ALL
4 SELECT TO_DATE('2019-08-22 18:30', 'yyyy-mm-dd hh24:mi') d FROM DUAL);
D ROUND(D,'HH') TRUNC(D,'HH')
------------------- ------------------- -------------------
2019-08-10 08:25:00 2019-08-10 08:00:00 2019-08-10 08:00:00
2019-08-22 18:30:00 2019-08-22 19:00:00 2019-08-22 18:00:00
To this point all of the results have been to standard and built-in increments: days, years, hours; you can do quarters, ISO weeks, minutes, etc. as well. Sometimes though, you might need to round dates to other increments, such as half-hour, 10-minute, or 15-minutes. Usually when people use the word “round” in these contexts they really are looking for a CEILing or FLOOR value.
SQL> -- "Round" to the half-hour
SQL> SELECT d,
2 TRUNC(d, 'hh24') + CEIL((TO_CHAR(d, 'mi') - 29) / 30) * 30 / 1440 round_down,
3 TRUNC(d, 'hh24') + FLOOR((TO_CHAR(d, 'mi') + 30) / 30) * 30 / 1440 round_up
4 FROM ( SELECT TO_DATE('2019-08-10 08:20', 'yyyy-mm-dd hh24:mi') + LEVEL*7 / 1440 d
5 FROM DUAL
6 CONNECT BY LEVEL <= 10);
D ROUND_DOWN ROUND_UP
------------------- ------------------- -------------------
2019-08-10 08:27:00 2019-08-10 08:00:00 2019-08-10 08:30:00
2019-08-10 08:34:00 2019-08-10 08:30:00 2019-08-10 09:00:00
2019-08-10 08:41:00 2019-08-10 08:30:00 2019-08-10 09:00:00
2019-08-10 08:48:00 2019-08-10 08:30:00 2019-08-10 09:00:00
2019-08-10 08:55:00 2019-08-10 08:30:00 2019-08-10 09:00:00
2019-08-10 09:02:00 2019-08-10 09:00:00 2019-08-10 09:30:00
2019-08-10 09:09:00 2019-08-10 09:00:00 2019-08-10 09:30:00
2019-08-10 09:16:00 2019-08-10 09:00:00 2019-08-10 09:30:00
2019-08-10 09:23:00 2019-08-10 09:00:00 2019-08-10 09:30:00
2019-08-10 09:30:00 2019-08-10 09:30:00 2019-08-10 10:00:00
SQL> -- "Round" to the quarter-hours
SQL> SELECT d,
2 TRUNC(d, 'hh24') + CEIL((TO_CHAR(d, 'mi') - 14) / 15) * 15 / 1440 round_down,
3 TRUNC(d, 'hh24') + FLOOR((TO_CHAR(d, 'mi') + 15) / 15) * 15 / 1440 round_up
4 FROM ( SELECT TO_DATE('2019-08-10 08:20', 'yyyy-mm-dd hh24:mi') + LEVEL*7 / 1440 d
5 FROM DUAL
6 CONNECT BY LEVEL <= 10);
D ROUND_DOWN ROUND_UP
------------------- ------------------- -------------------
2019-08-10 08:27:00 2019-08-10 08:15:00 2019-08-10 08:30:00
2019-08-10 08:34:00 2019-08-10 08:30:00 2019-08-10 08:45:00
2019-08-10 08:41:00 2019-08-10 08:30:00 2019-08-10 08:45:00
2019-08-10 08:48:00 2019-08-10 08:45:00 2019-08-10 09:00:00
2019-08-10 08:55:00 2019-08-10 08:45:00 2019-08-10 09:00:00
2019-08-10 09:02:00 2019-08-10 09:00:00 2019-08-10 09:15:00
2019-08-10 09:09:00 2019-08-10 09:00:00 2019-08-10 09:15:00
2019-08-10 09:16:00 2019-08-10 09:15:00 2019-08-10 09:30:00
2019-08-10 09:23:00 2019-08-10 09:15:00 2019-08-10 09:30:00
2019-08-10 09:30:00 2019-08-10 09:30:00 2019-08-10 09:45:00
While ROUND, TRUNC, CEIL, and FLOOR are basic functions fundamental to SQL development , hopefully this guide helped illustrate some additional options to expand their usage within your applications.