ROUND and TRUNC Functions(with numbers)

Oracle ROUND and TRUNC functions

Oracle/PLSQL: TRUNC Function (with numbers)
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.

Applies To

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

The query shows the value stored in the TOTAL_SALE_AMOUNT column in the first results column labeled ORIG. The second column is the TOTAL_SALE_AMOUNT plus 10% for shipping and handling. Notice that in some cases, the number has three digits after the decimal point. The third column is the second column with the ROUND function applied to round to two places after the decimal place.

This query shows the calculated value in the first column (same as before: TOTAL_SALE_AMOUNT plus 10%). In the next column, you see the round value as before. The third column shows the calculated value with the TRUNC function applied to truncate to two places after the decimal point.

This query changes both the ROUND and TRUNC parameter from 2 to 0, so that each calculated value is rounded and truncated to a whole number.

This final query changes the ROUND and TRUNC parameter from zero to -2, which rounds and truncates to the hundreds (two decimal places to the left of the decimal).