Oracle ROUND and TRUNC functions

The Oracle ROUND and TRUNC functions are essential mathematical functions used in the Oracle Database system to manipulate numerical and date values. These functions serve different purposes and help users in managing and processing data with precision and efficiency.
ROUND Function: The purpose of the ROUND function is to round a given number or date to a specified decimal place or unit. In the case of numerical values, the ROUND function rounds the input value to the nearest whole number, or to a specified number of decimal places. For date values, it rounds to the nearest day, month, or year based on the input unit.
Syntax for ROUND function with numbers:

ROUND(number, decimal_places)

Syntax for ROUND function with dates:

ROUND(date, unit)

The TRUNC function, on the other hand, is used to truncate a given number or date to a specified decimal place or unit. For numerical values, the TRUNC function removes the fractional part of the number, effectively rounding down to the nearest whole number, or to a specified number of decimal places. For date values, it truncates to the nearest day, month, or year based on the input unit, effectively discarding the smaller units.
Syntax for TRUNC function with numbers:

TRUNC(number, decimal_places)

TRUNC(date, unit)

In summary, the ROUND and TRUNC functions in Oracle serve the purpose of rounding and truncating numerical and date values, respectively. These functions are crucial in data processing, analysis, and reporting, allowing users to handle large sets of data with greater precision and control.

This Oracle tutorial explains how to use the Oracle/PLSQL TRUNC function with syntax and examples.
Description: The Oracle/PLSQL TRUNC function returns a number truncated to a certain number of decimal places.
Syntax (with numbers) : The syntax for the TRUNC function in Oracle/PLSQL is:

TRUNC( number [, decimal_places] )

Parameters or Arguments : number
The number to truncate.decimal_placesOptional. The number of decimal places to truncate to. This value must be an integer. If this parameter is omitted, the TRUNC function will truncate the number to 0 decimal places.

The TRUNC function can be used in the following versions of Oracle/PLSQL:

(*)Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example: (with numbers)
Let us look at some Oracle TRUNC function examples and explore how to use the TRUNC function in Oracle/PLSQL.
For example:

TRUNC(124.815)
Result: 124

TRUNC(124.815, 0)
Result: 124

TRUNC(124.815, 1)
Result: 124.8

TRUNC(124.815, 2)
Result: 124.81

TRUNC(124.815, 3)
Result: 124.815

TRUNC(-124.815, 2)
Result: -124.81

TRUNC(124.815, -1)
Result: 120

TRUNC(124.815, -2)
Result: 100

TRUNC(124.815, -3)
Result: 0