Oracle TRUNC Date Function By Practical Examples

The Oracle TRUNC() function returns a DATE value truncated to a specified unit.

Syntax

The following shows the syntax of the Oracle TRUNC() function:

TRUNC(date, format)Code language: SQL (Structured Query Language) (sql)

Arguments

The TRUNC() function accepts two arguments:

1) date
The date argument is a DATE value or an expression that evaluates to a DATE value that will be truncated.

2) format
The format argument determines the unit to which the date will be truncated.

The format argument is optional. Its default value is DD that instructs the TRUNC() function to truncate the date to midnight.

The following table illustrates valid values for the format argument:

Format	Description
CC, SCC	Century, with or without minus sign (BC)
[S]YYYY, [S]YEAR, YYY, YY, Y	Year (in various appearances)
IYYY, IYY, IY, I	ISO year
Q	Quarter
MONTH, MON, MM, RM	Month (full name, abbreviated name, numeric, Roman numerals)
IW, WW (ISO)	week number
W	Day of the week
DDD, DD, J	Day (of the year/of the month/Julian day)
DAY, DY, D	Closest Sunday
HH, HH12, HH24	Hours
MI	Minutes

Return value

The TRUNC() function returns a DATE value truncated to a specified unit.

Examples

Let’s look at some examples of using the Oracle TRUNC() function.

A) Truncate a date value using the default format

Consider the following date and time value:

04-Aug-2017 15:35:32Code language: SQL (Structured Query Language) (sql)

The following statement truncates the date value to midnight:

SELECT
  TO_CHAR( 
    TRUNC(TO_DATE( '04-Aug-2017 15:35:32 ', 'DD-Mon-YYYY HH24:MI:SS' )), 
    'DD-Mon-YYYY HH24:MI:SS' 
  ) result
FROM
  dual; Code language: SQL (Structured Query Language) (sql)

Output:

RESULT              
--------------------
04-Aug-2017 00:00:00Code language: CSS (css)

In this example,

First, the TO_DATE() function converted a date string to a DATE value.
Second, the TRUNC() function truncated the date. Because we did not pass the format argument, the TRUNC() function uses the default value that truncates the date to midnight.
Third, the TO_CHAR() function formatted the result of the TRUNC() function.

B) Get the first day of the month of a date

The following statement returns the first day of the current month.

SELECT
  TRUNC( SYSDATE, 'MM' ) result
FROM
  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT   
---------
01-MAY-20

If you want to return the first day of the month of a specific date, you just need to use that date instead of the SYSDATE

C) Get the first day of the quarter of a date

Similarly, you can get the first day of the current quarter:

SELECT TRUNC( SYSDATE, 'Q' ) result
FROM  dual;Code language: SQL (Structured Query Language) (sql)

Output:

RESULT   
---------
01-APR-20

In this example, we replaced the month ( MM) by quarter ( Q).

In this tutorial, you have learned how to use the Oracle TRUNC() function to truncate a date value to a specified unit.

Was this tutorial helpful?