Oracle TRUNC Function Explained with Examples

The TRUNC function is one of the most common and versatile functions in Oracle. Let’s take a look at how it’s used and some examples.

 

Purpose of the Oracle TRUNC Function

The purpose of the Oracle TRUNC function is to truncate either a number or a date value.

The TRUNC function works on two different data types – number or date. This means you can provide it with either of these two data types, and the TRUNC function will perform slightly differently and give you a result for that data type.

If you supply it with a date data type, then it will return the portion of that value as specified by your format model. If you supply it with a numeric data type, it will round the number to the specified number of decimal places.

Let’s take a look at how this is used.

 

Syntax

The syntax of the TRUNC function is slightly different, depending on which data format you’re supplying. In both cases, there are two parameters.

Using a date value, the syntax is:

TRUNC( date, fmt )

Using a number, the syntax is:

TRUNC( number, decimals )

Let’s take a look at the parameters for this function.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

 

Parameters

The parameters of the Oracle TRUNC function for dates are:

• date (mandatory): This is the date or datetime value that is to be truncated or reduced to.
• fmt (optional): This is the format that the date value is to be truncated to. If it is not specified, then the function will truncate the date value to the nearest day. See below for the valid format parameters that can be used.

The parameters of the Oracle TRUNC function for numbers are:

• number (mandatory): This is the numeric value that is to be truncated.
• decimals (optional): This is the number of places to the left or right of the decimal point to truncate the number value. If it is positive, digits to the right are truncated, and if it is negative, digits to the left are truncated. If it is omitted, number is then truncated to a whole number (0 decimal places).

If you use the date version of the TRUNC function, then the value returned is always of datatype date, even if you use a different datetime datatype as the parameter.

If you use the number version of the TRUNC function, then the returned value is the same as the number parameter if you don’t specify the decimals. If you do specify the decimals, then it returns a data type of NUMBER.

 

Available Formats for Date TRUNC

These are the available format values for using the TRUNC function on dates. You can specify these as part of the second parameter when using the TRUNC function on dates.

Unit	Valid Format Parameters
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year	IYYY, IY, I
Quarter	Q
Month	MONTH, MON, MM, RM
Week	WW
Week Number	IW
Week	W
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI

 

Using Oracle TRUNC on SYSDATE

The Oracle TRUNC function is often used on the SYSDATE keyword. It’s something I do quite often. It’s useful to get the current date, without the time, by using the SYSDATE keyword.

You can use it by simply running:

TRUNC(SYSDATE)

See below for more examples about using TRUNC with SYSDATE.

 

Can You Use Oracle TRUNC on a TIMESTAMP?

Yes, you can. The DATE version of the TRUNC function accepts any date data type, including timestamp. You can then truncate it to the format you need.

As of Oracle version 9 (version 9.2.0.3 I believe), you can perform TRUNC on timestamp. Before this version, you got an error (ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP).

If you are unable to use TRUNC on timestamps in your version, you can CAST it to a DATE first:

TRUNC(CAST(TIMESTAMP as DATE))

See below for some examples of using TRUNC with TIMESTAMP.

 

Can You Use Oracle TRUNC on a STRING?

No, you can’t use the Oracle TRUNC function on a string data type. If you want to remove characters from a string data type, you can use the SUBSTR function

 

Examples of the Oracle TRUNC Function

Here are some examples of the TRUNC function. I find that examples are the best way for me to learn about code, even with the explanation above.

These examples use a table called “datelist” that I created using the following code:

CREATE TABLE datelist (
 datevalue DATE,
 timestampvalue TIMESTAMP
 );
INSERT INTO datelist (datevalue, timestampvalue)
VALUES (SYSDATE, SYSTIMESTAMP);
SELECT datevalue, timestampvalue FROM datelist;

DATEVALUE	TIMESTAMPVALUE
26/MAR/22	26/MAR/22 05:58:01.378000000 AM

 

Example 1

This example shows how to use TRUNC on a date to truncate it to a year.

SELECT TRUNC(datevalue, 'YEAR')
FROM datelist;

Result:

01/JAN/22

It shows the date value converted to a year, which is the first day of the first month of the year.

 

Example 2

This example uses TRUNC on a date to truncate it into a month.

SELECT TRUNC(datevalue, 'MONTH')
FROM datelist;

Result:

01/MAR/22

It shows a similar result, converting the date to a month and year value, which changes the day to the first of that month.

 

Example 3

This example uses TRUNC on a date to truncate it to a day.

SELECT TRUNC(datevalue, 'DD')
FROM datelist;

Result:

26/MAR/22

It looks to be the same value that is returned, because the provided value is already a day/month/year type.

 

Example 4

This example uses TRUNC to show the date value as an IW format (which is the week number in a year).

SELECT TRUNC(datevalue, 'IW')
FROM datelist;

Result:

23/MAR/22

It has truncated the value back to March 23, which is the Monday of the same week.

 

Example 5

This example shows how to use TRUNC on SYSDATE.

SELECT TRUNC(SYSDATE)
FROM dual;

Result:

26/MAR/22

It looks to be the same value that is returned because SYSDATE is already in that format.

 

Example 6

This example uses TRUNC on a TIMESTAMP value.

SELECT TRUNC(timestampvalue)
FROM datelist;

Result:

26/MAR/22

As you can see, it has removed the time value from the timestamp and just returned a date.

 

Example 7

This example uses a TIMESTAMP and shows it as months.

SELECT TRUNC(timestampvalue, 'MM')
FROM datelist;

Result:

01/MAR/15

This shows the timestamp value truncated to the month, which is March.

 

Example 8

This example shows TRUNC using a number.

SELECT TRUNC(5718.46987)
FROM dual;

Result:

5718

The number is truncated to a whole number. The decimals are removed, which is done without rounding.

 

Example 9

This example truncates the number to 2 places.

SELECT TRUNC(5718.46987, 2)
FROM dual;

Result:

5718.46

The result shows the original number with two decimal places, without any rounding.

 

Example 10

This example truncates the number to 3 places to the left of the decimal.

SELECT TRUNC(5718.46987, -3)
FROM dual;

Result:

5000

This value is the result of truncating the 3 decimal places, so it only shows the thousands value.

 

Example 11

This example truncates the number using a decimal value of 0.

SELECT TRUNC(5718.46987, 0)
FROM dual;

Result:

5718

This is the same as providing no decimal places, as it rounds to a whole number.

 

Similar Functions

Some functions which are similar to the Oracle TRUNC function are:

• TRIM – removes the leading and trailing characters from a string
• SUBSTR – returns a part of the provided value, based on the specified position and length
• ROUND – rounds a value to the specified number of places.

You can find a full list of Oracle functions here.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: