About the ROUND and TRUNC Functions

The ROUND and TRUNC functions are useful tools for adjusting values in Oracle SQL. This article explains their usage and purposes in a way that is easy for beginners to understand. Furthermore, it introduces practical applications, including examples for not only numeric data but also for date-type data.

This article uses examples based on the SCOTT sample schema.
You can import and use the SCOTT sample schema by executing the following:

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger

The ROUND Function
The TRUNC Function
Difference between ROUND and TRUNC
Points to Note
Conclusion

The ROUND Function

ROUND adjusts numbers or dates by “rounding” them. The value is rounded to the place specified by the second argument. If not specified, it rounds to the nearest whole number (or the first decimal place, depending on context).

ROUND Syntax

SELECT ROUND(number, precision) FROM table;

Practical Example with Numbers

The following query rounds an employee’s salary to the nearest tens place.

SELECT empno, sal, ROUND(sal, -1) AS rounded_sal
FROM emp
WHERE empno=7566;

Execution Result

SQL> SELECT empno, sal, ROUND(sal, -1) AS rounded_sal
  2  FROM emp
  3  WHERE empno=7566;

     EMPNO        SAL ROUNDED_SAL
---------- ---------- -----------
      7566       2975        2980

Practical Example with Dates

ROUND can also be used to adjust dates. In the following example, the date is rounded to the nearest month.

SELECT hiredate, ROUND(hiredate, 'MONTH') AS rounded_date
FROM emp;

Execution Result

SQL> SELECT hiredate, ROUND(hiredate, 'MONTH') AS rounded_date
  2  FROM emp;

HIREDATE  ROUNDED_D
--------- ---------
17-DEC-80 01-JAN-81
20-FEB-81 01-MAR-81
22-FEB-81 01-MAR-81
02-APR-81 01-APR-81
28-SEP-81 01-OCT-81
01-MAY-81 01-MAY-81
09-JUN-81 01-JUN-81
19-APR-87 01-MAY-87
17-NOV-81 01-DEC-81
08-SEP-81 01-SEP-81
23-MAY-87 01-JUN-87
03-DEC-81 01-DEC-81
03-DEC-81 01-DEC-81
23-JAN-82 01-FEB-82

14 rows selected.

Notes on ROUND

Numeric Type: Can be adjusted by specifying the decimal place.
Date Type: Can specify date formats like ‘YEAR’ or ‘MONTH’.
Often used in financial calculations and data rounding processes.

The TRUNC Function

TRUNC adjusts values by “truncating” them. The difference from ROUND is that it cuts off the value at the specified place without rounding.

TRUNC Syntax

SELECT TRUNC(number, precision) FROM table;

Practical Example with Numbers

The following query truncates an employee’s salary to the tens place.

SELECT empno, sal, TRUNC(sal, -1) AS truncated_sal
FROM emp
WHERE empno=7566;

Execution Result

SQL> SELECT empno, sal, TRUNC(sal, -1) AS truncated_sal
  2  FROM emp
  3  WHERE empno=7566;

     EMPNO        SAL TRUNCATED_SAL
---------- ---------- -------------
      7566       2975          2970

Practical Example with Dates

It is also possible to truncate dates. In the following example, the date is truncated to the first day of the month.

SELECT hiredate, TRUNC(hiredate, 'MONTH') AS truncated_date
FROM emp;

Execution Result

SQL> SELECT hiredate, TRUNC(hiredate, 'MONTH') AS truncated_date
  2  FROM emp;

HIREDATE  TRUNCATED
--------- ---------
17-DEC-80 01-DEC-80
20-FEB-81 01-FEB-81
22-FEB-81 01-FEB-81
02-APR-81 01-APR-81
28-SEP-81 01-SEP-81
01-MAY-81 01-MAY-81
09-JUN-81 01-JUN-81
19-APR-87 01-APR-87
17-NOV-81 01-NOV-81
08-SEP-81 01-SEP-81
23-MAY-87 01-MAY-87
03-DEC-81 01-DEC-81
03-DEC-81 01-DEC-81
23-JAN-82 01-JAN-82

14 rows selected.

Notes on TRUNC

Numeric Type: Applied when truncating values without rounding down (or up).
Date Type: Can truncate dates to a specific unit.
Useful for adjusting information granularity and data analysis.

Difference between ROUND and TRUNC

Item	ROUND	TRUNC
Behavior	Rounds	Truncates
Main Use	Precise rounding calculations	Adjusting granularity
Date Support	Supported	Supported

Points to Note

ROUND is suitable for decimal rounding calculations, while TRUNC is intended for simple truncation of values.
Be careful, as specifying the required place incorrectly can lead to unintended results.

Conclusion

ROUND and TRUNC are extremely useful functions for adjusting numeric and date data. Please understand their respective characteristics and apply them appropriately in actual system design and data processing. In particular, their application to date data is diverse, so please be sure to try them out.

[reference]
Oracle Database SQL Language Reference, 19c