Online Tech Support

This Online Tech Support page is based on examples to be easier to follow. Function Oracle Trunc will remove (truncate) numbers after or before decimal places from a number or a time or date part from a date value. The removed part can be limited by an optional declared portion. This declared portion is a second parameter in the Trunc function and when it is left empty it will remove all decimal places from a number or a time part from a date. The syntax to use Oracle Trunc with a date is:

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

The first example with Oracle Trunc will remove from a date all values up to the month unit. The day cannot be zero so it will be replaced with the 1st day.

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

The second example is removing minutes and seconds from the date value.

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

The second group of truncating is done by using number. When you will apply function Oracle Trunc on a number and the second parameter is a positive number it will remove all decimal places up to the place declared in that parameter. Declaring a negative number as the second parameter will truncate values up to before the decimal place. As we did mention before leaving the second parameter not declared will truncate all number after decimal place. The syntax of Oracle Trunc with numbers is:

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

The next example will demonstrate how the function will leave only two number places after the decimal.

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

The second Oracle Trunc with numbers example is done using a negative parameter and it will start to count removing places from left to right. The example below will remove or replace with zeros all number places up to hundreds as the parameter is “-2” and the “111.234” will become “100“.

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

The second parameter plays in Trunc very important role, because when it is negative the value will be truncated from left to right from the decimal place and when it is positive then the value will be truncated from right to left and the counting point is the decimal place.


See Also:
Home Oracle Select