How To Use Function Trunc In Oracle

This online help page is based on examples to make it easier to follow. Function Oracle Trunc allows to removing or truncating a portion of value. The value type can be for example a date or a number. Oracle Trunc function removes by default from a date all time part and from a number all decimal places. The syntax of Trunc in Oracle for a date type is:

TRUNC (<date>,<date format model>)

In the following Oracle DBA example the Oracle Trunc will remove from the date all days and time and since the day number cannot be 0 it will be replaced with number 1.

SELECT sysdate, TRUNC(sysdate,'MONTH')
  FROM dual;

The second example with Oracle Trunc will remove all minutes and seconds from the date value. The removed minutes and seconds are to be replaced with 0-s .

SELECT sysdate, TRUNC(sysdate,'HH')
  FROM dual;

When function Oracle Trunc will be used with a number it will remove by default all decimal places from it and this happens when the second parameter parameter is left empty. Otherwise the function will truncate up to declared position. The syntax of Oracle Trunc with a number type is following:

TRUNC (<number>[,<how many places after decimals to remove>])

The first example with Oracle Trunc and a number will remove all numbers after two places after decimal. Take a look at the example the number is “111.234” and to declare the 2 places after decimal we added number “2” after the coma in the TRUNC function.

SELECT 111.234, TRUNC(111.234,2)
  FROM dual;

The second parameter of the Trunc function can be also a negative number. In that case the Oracle Trunc will start to remove places from left to right from the decimal place. The situation is described in the following Oracle DBA example. The number is “111.234” as in the last example and the second parameter is “-2” (minus two). The Trunc function will remove number “11.234” because it works from left to right starting from the decimal point. And the number will become after truncating “100“.

SELECT 111.234, TRUNC(111.234,-2)
  FROM dual;

As you saw from the exampled above the Oracle Trunc can remove values from right to left or from left to right and this depends on the second parameter if it is positive or negative. This is very important thing to keep in mind when you are using function Trunc in Oracle programs.
See Also: Oracle Date Format Oracle Home