FB

Oracle TRUNC FunctionThe 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 a date data type, then it will return the portion of that value as specified by your format model. If you supply it 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.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

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 on 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/15 26/MAR/15 05:58:01.378000000 AM

And yes, it is about 6AM when I’m writing this post!

Example 1

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

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

Result:

TRUNCVALUE
01/JAN/15

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') AS TRUNCVALUE
FROM datelist;

Result:

TRUNCVALUE
01/MAR/15

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') AS TRUNCVALUE
FROM datelist;

Result:

TRUNCVALUE
26/MAR/15

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') AS TRUNCVALUE
FROM datelist;

Result:

TRUNCVALUE
23/MAR/15

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) AS TRUNCVALUE
FROM dual;

Result:

TRUNCVALUE
26/MAR/15

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) AS TRUNCVALUE
FROM datelist;

Result:

TRUNCVALUE
26/MAR/15

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') AS TRUNCVALUE
FROM datelist;

Result:

TRUNCVALUE
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) AS TRUNCVALUE
FROM dual;

Result:

TRUNCVALUE
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) AS TRUNCVALUE
FROM dual;

Result:

TRUNCVALUE
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) AS TRUNCVALUE
FROM dual;

Result:

TRUNCVALUE
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) AS TRUNCVALUE
FROM dual;

Result:

TRUNCVALUE
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.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Image courtesy of digitalart / FreeDigitalPhotos.net

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Get Your FREE PDF: 9 Ways to Improve your Database Skills