Oracle TRUNC

TRUNC is an Oracle function used for truncating the value up to n decimal places after the decimal point. If we specify n as 0 then truncation happens only up to the digit before the decimal point.

Unlike the ROUND function, TRUNC function simply truncates the value and makes no changes to the value itself.

Syntax

TRUNC( expression ,n )

Here,

Expression – Expression could be a column, numerical value or even date.

n – Number of digits after the decimal point up to which a value can truncated.

Let’s see some examples with TRUNC function.

1. Truncating a value

Lets use TRUNC function to truncate a few decimal values to see how it works. Lets see some queries:

SELECT TRUNC(82.282,2) FROM dual;
SELECT TRUNC(82.288,2) FROM dual;
SELECT TRUNC(82.292,0) FROM dual;

SELECT TRUNC(82.282,2),TRUNC(82.288,2),TRUNC(82.292,0) FROM dual;

Result:

2. TRUNC for truncating values in a Column

Here, we will be using Employee table from HR schema for this example:

Suppose, we want to perform a calculation on Salary table in HR column and truncate the calculated values then we use this query:

SELECT 
    first_name, last_name , 
    TRUNC(salary/1000, 2) "SALARY (IN THOUSANDS)"
FROM employees ;

Result:

We use this query to first convert the salary in thousands and then we simply truncate all the values up to 2 digits.

3. Using TRUNC function with Dates

TRUNC function can also be used with dates to truncate date time values to a specified unit or format.

Syntax:

TRUNC(date, format);

Here,

date – Date is the date time value that we will truncate.

format – We can specify the format in which we would like the date to be truncated. This is optional.

Some of the valid formats for Date are as follows:

Suppose, we have a date time value in this format ( as a character string):

’14-Mar-2012 16:30:18′

First, we need to convert this value to DATE data type as this value is considered a string of characters. For this we need to use, TO DATE function.

TO_DATE( '14-Mar-2012 16:30:18', 'DD-Mon-YYYY HH24:MI:SS' )

This function converts the character string to DATE data type in the format we want.

3.1 Using TRUNC function

Lets truncate the date to midnight:

SELECT
TO_CHAR(
TRUNC(TO_DATE( '14-Mar-2012 16:30:18 ', 'DD-Mon-YYYY HH24:MI:SS' )),
'DD-Mon-YYYY HH24:MI:SS'
) result
FROM
dual;

Result:

In this example, we were able to truncate the date to midnight using 3 steps:

1. First, we used TO DATE function to convert date character string to Date value.
2. Then, we used TRUNC function to truncate that date. Since, we did not specify a date value format, Oracle by default truncated the value to midnight.
3. Finally, we used TO CHAR function to convert the date value back to character string format.

3.2 Using TRUNC function to get first day of the year

SYSDATE is an inbuilt function in Oracle which extracts the current date and time from the PC on which Oracle is installed.

Here, we use TRUNC function to find out the first date of the year:

SELECT
TRUNC( SYSDATE, 'yyyy' ) result
FROM
dual;

Result:

We can also use a similar query for finding the first day of month, or quarter as well by specifying the required format.

In this tutorial, we learned how to use TRUNC function with decimals, columns and dates.