Oracle TRUNC

Summary: In this tutorial, you’ll learn how to use the Oracle TRUNC function to truncate date to a specific unit.

Introduction to Oracle TRUNC function #

The Oracle TRUNC() function returns a DATE value truncated to a specific component (e.g., day, month, year), and zeroing out smaller components like times or later date parts.

Here’s the syntax of the Oracle TRUNC() function:

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

The TRUNC() function accepts two arguments:

• The date argument is a DATE value or an expression that evaluates to a DATE value that will be truncated.
• 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 shows valid values for the format argument:

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

Oracle TRUNC function examples #

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

Truncating 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 converts a date string to a DATE value.
• Second, the TRUNC() function truncates the date. Since we do not pass the format argument, the TRUNC() function uses the default value that truncates the date to midnight.
• Third, use the TO_CHAR() function formats the result of the TRUNC() function.

Getting the first day of the month of a date #

The following statement uses the TRUNC function to return 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

Getting the first day of the quarter of a date #

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

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

Using TRUNC() function with table data #

The following query uses the TRUNC function in the WHERE clause of a statement to retrieve orders placed on April 09, 2017:

SELECT
  order_id,
  customer_id,
  order_date
FROM
  orders
WHERE
  TRUNC (order_date) = TO_DATE ('2017-04-09', 'YYYY-MM-DD');Code language: JavaScript (javascript)

Output:

  ORDER_ID CUSTOMER_ID ORDER_DAT
---------- ----------- ---------
         5           5 09-APR-17

In this example, the TRUNC(order_date) removes the time part so that the query performs comparison only by date.

Summary #

• Use the Oracle TRUNC() function to truncate a date value to a specified unit.