Advanced Oracle SQL: Rounding and Truncating Functions Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

SELECT
   X.COLUMN_VALUE X,
   ROUND(X.COLUMN_VALUE) "ROUND(X)",
   TRUNC(X.COLUMN_VALUE) "TRUNC(X)",
   FLOOR(X.COLUMN_VALUE) "FLOOR(X)",
   CEIL(X.COLUMN_VALUE) "CEIL(X)"
FROM
   TABLE(SYS.ODCINUMBERLIST(-9.9,-1.1,-0.5,0.5,1.1,9.9)) X;
         X   ROUND(X)   TRUNC(X)   FLOOR(X)    CEIL(X)
---------- ---------- ---------- ---------- ----------
      -9.9        -10         -9        -10         -9
      -1.1         -1         -1         -2         -1
       -.5         -1          0         -1          0
        .5          1          0          0          1
       1.1          1          1          1          2
       9.9         10          9          9         10

TRUNC removes the decimal part for both negatives and positives. Therefore, TRUNC(-x) is equal to -TRUNC(x). ROUND returns the closest value and when both upper and lower values are equidistant, it rounds up to the higher positive value or the lower negative value, i.e. ROUND(-x)=-ROUND(x).

FLOOR(x) returns the highest integer that is not greater than x (the lower bound) and CEIL(x) returns the lowest integer that is not less than x (the upper bound).

ROUND and TRUNC accept a second argument to round or truncate at another decimal place than the default unit:

SELECT
   X.COLUMN_VALUE X,
   ROUND(X.COLUMN_VALUE, -2) "ROUND(X,-2)",
   TRUNC(X.COLUMN_VALUE, -2) "TRUNC(X,-2)",
   ROUND(X.COLUMN_VALUE, 0) "ROUND(X)",
   TRUNC(X.COLUMN_VALUE, 0) "TRUNC(X)",
   ROUND(X.COLUMN_VALUE, 2) "ROUND(X,2)",
   TRUNC(X.COLUMN_VALUE, 2) "TRUNC(X,2)",
FROM
   TABLE(SYS.ODCINUMBERLIST(1234.4321,-9876.6789)) X;
         X ROUND(X,-2) TRUNC(X,-2)   ROUND(X)   TRUNC(X) ROUND(X,2) TRUNC(X,2)
---------- ----------- ----------- ---------- ---------- ---------- ----------
 1234.4321        1200        1200       1234       1234     1234.43    1234.43
-9876.6789       -9900       -9800      -9877      -9876    -9876.68   -9876.67

The second argument is an integer that defines at which decimal place the rounding takes place, so ROUND(x, 2) will round to the hundredth.

ROUND and TRUNC also work for dates:

SELECT
   SYSDATE,
   ROUND(SYSDATE),
   TRUNC(SYSDATE)
FROM
   DUAL;
SYSDATE             ROUND(SYSDATE)      TRUNC(SYSDATE)
------------------- ------------------- -------------------
25.01.2008 21:08:26 26.01.2008 00:00:00 25.01.2008 00:00:00

Unfortunately, TIMESTAMP is cast to DATE when using TRUNC and ROUND.

TRUNC and ROUND accept a second argument to truncate or round to a specific period of time:

SELECT
   D.COLUMN_VALUE "D",
   FMT.COLUMN_VALUE "FMT",
   ROUND(D.COLUMN_VALUE, FMT.COLUMN_VALUE) "ROUND(D,FMT)",
   TRUNC(D.COLUMN_VALUE, FMT.COLUMN_VALUE) "TRUNC(D,FMT)"
FROM
   TABLE(SYS.ODCIDATELIST(TIMESTAMP '1994-08-18 12:30:30.00')) D,
   TABLE
   (
      SYS.ODCIVARCHAR2LIST
      (
         'MI', 'HH', 'DD', 'D', 'W', 'WW', 'IW', 'MM', 'Q', 'Y', 'IY', 'CC'
      )
   ) FMT;

D                   FMT ROUND(D,FMT)        TRUNC(D,FMT)
------------------- --- ------------------- -------------------
18.08.1994 12:30:30 MI  18.08.1994 12:31:00 18.08.1994 12:30:00
18.08.1994 12:30:30 HH  18.08.1994 13:00:00 18.08.1994 12:00:00
18.08.1994 12:30:30 DD  19.08.1994 00:00:00 18.08.1994 00:00:00
18.08.1994 12:30:30 D   21.08.1994 00:00:00 14.08.1994 00:00:00
18.08.1994 12:30:30 W   22.08.1994 00:00:00 15.08.1994 00:00:00
18.08.1994 12:30:30 WW  20.08.1994 00:00:00 13.08.1994 00:00:00
18.08.1994 12:30:30 IW  22.08.1994 00:00:00 15.08.1994 00:00:00
18.08.1994 12:30:30 MM  01.09.1994 00:00:00 01.08.1994 00:00:00
18.08.1994 12:30:30 Q   01.10.1994 00:00:00 01.07.1994 00:00:00
18.08.1994 12:30:30 Y   01.01.1995 00:00:00 01.01.1994 00:00:00
18.08.1994 12:30:30 IY  02.01.1995 00:00:00 03.01.1994 00:00:00
18.08.1994 12:30:30 CC  01.01.2001 00:00:00 01.01.1901 00:00:00

MI truncates to the minute, HH to the hour, DD to the day, MM to the month, Q to the quarter, Y to the year and CC to the century. When rounding, MM rounds up from the 16th day and Q from the 16th day of the second month. IW is fairly useful as it truncates to Monday regardless of session parameters.

D is the first day of the week, which is Sunday when the territory is America. W is the week of the month and WW is the week of year.

SELECT
   DATE '2008-01-01',
   DATE '2008-02-01'
FROM
   DUAL;
DATE'2008-01-01'       DATE'2008-02-01'      
---------------------- ----------------------
Tuesday   01 January   Friday    01 February

2008 starts on a Tuesday, February starts on a Friday and the territory is set to America:

SELECT
   TRUNC(DATE '2008-02-14','IW') IW,
   TRUNC(DATE '2008-02-14','W') W,
   TRUNC(DATE '2008-02-14','WW') WW,
   TRUNC(DATE '2008-02-14','D') D
FROM
   DUAL;
IW         W          WW         D
---------- ---------- ---------- ----------
Mon 11 Feb Fri 8 Feb  Tue 12 Feb Sun 10 Feb

IW truncates to Monday (always), W to Friday (1st day of month), WW to Tuesday (1st day of year) and D to Sunday (in America).

Rather than using TO_NUMBER(TO_CHAR(d, 'D')) to get the day of the week, it is possible to use TRUNC:

SELECT
   TRUNC(SYSDATE)-TRUNC(SYSDATE,'IW') "DAY"
FROM
   DUAL;
TRUNC(SYSDATE)-TRUNC(SYSDATE,'IW')
----------------------------------
                                 4

Monday is 0 and Sunday is 6. The main advantage of this method is that it can be used in materialized views, function-based indexes and session-independent views and procedures.

SELECT
   COLUMN_VALUE N,
   ROUND(COLUMN_VALUE*20)/20 "ROUND(N)"
FROM
   TABLE(SYS.ODCINUMBERLIST(1.12, 2.53, 5.25));
         N      ROUND(N)
---------- -------------
      1.12          1.10
      2.53          2.55
      5.25          5.25

The value is multiplied by 20 before rounding. The rounding is then done at a five-hundredths granularity.

SELECT
   COLUMN_VALUE D,
   DATE '2000-01-01'+
      TRUNC((COLUMN_VALUE - DATE '2000-01-01')*96)/96 "TRUNC(D)"
FROM
   TABLE(SYS.ODCIDATELIST(
      TIMESTAMP '2009-02-21 05:25:45',
      TIMESTAMP '2009-02-25 06:31:30'
  ));

D                   TRUNC(D)
------------------- -------------------
2009-02-21 05:25:45 2015-02-21 05:15:00
2009-02-25 06:31:30 2015-02-25 06:30:00

The difference with an arbitrary date is multiplied by 96 to split the day in 96 periods of 15 minutes (24 hours times 4 quarters = 96 periods).

Some periods could be switched to default calendar periods by adding a period before the TRUNC:

SELECT
   COLUMN_VALUE D,
   TO_CHAR(TRUNC(ADD_MONTHS(COLUMN_VALUE, 3), 'Y'), '"FY"YY') "FY"
FROM
   TABLE
   (
      SYS.ODCIDATELIST(
         DATE '2008-09-30',
         DATE '2008-12-21',
         DATE '2009-02-25'
      )
   );

D          FY
---------- ----
2008-09-30 FY08
2008-12-21 FY09
2009-02-25 FY09

The fiscal year starts in October, three months before the calendar year. This three-month period is added before truncating the date.