Oracle TRUNC (Date) Function: A Simple Guide

The TRUNC function (for dates) in Oracle SQL is one of the most important and frequently used date functions. It "truncates" or "cuts off" a DATE or TIMESTAMP value down to a specified unit of time.

Its most common use by far is to remove the time portion from a date, setting the time to midnight (00:00:00).

What is the TRUNC Function for Dates?

The TRUNC(datetime, fmt) function takes a DATE or TIMESTAMP and returns the starting point of the unit specified by the format model fmt.

In simple terms, it always "rounds down":

• TRUNC(10:45 AM, 'HH') returns 10:00 AM.
• TRUNC('15-Mar-2025', 'MM') returns 01-Mar-2025.
• TRUNC('15-Mar-2025') (default) returns 15-Mar-2025 00:00:00.

Key Point: For DATE and TIMESTAMP values, the TRUNC and FLOOR functions are identical and can be used interchangeably.

The value returned is always a DATE data type, even if your input was a TIMESTAMP.

TRUNC (Date) Function Syntax

The syntax for TRUNC (Date) is:

TRUNC(datetime, [fmt])

Let's break that down:

• datetime: The date or timestamp value (or column) you want to truncate.
• [fmt] (Optional): The "format model" or unit you want to truncate to.
  • If omitted, this defaults to 'DD', which truncates to the start of the day (midnight).
  • Common fmt models:
    • 'DD': Truncates to the day (removes time).
    • 'MM' or 'MONTH': Truncates to the first day of the month.
    • 'YYYY' or 'YEAR': Truncates to the first day of the year (Jan 1st).
    • 'HH' or 'HH24': Truncates to the start of the hour.

A Note on Viewing Results

To see the time portion (or its removal) in the results, you should run this command in your SQL session first:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Oracle TRUNC (Date) Function Examples

Here are two practical examples of how to use TRUNC.

Example 1: Removing the Time from a Date with TRUNC (Default)

This is the most common use case for TRUNC. By not providing a format model, it defaults to 'DD' and sets the time to midnight. This is perfect for comparing dates without worrying about the time of day.

Query:

SELECT 
  SYSDATE AS "Now",
  TRUNC(SYSDATE) AS "Start_of_Today"
FROM DUAL;

Result: (Assuming "Now" is 07-NOV-2025 14:30:00)

Now                  Start_of_Today
-------------------- --------------------
07-NOV-2025 14:30:00 07-NOV-2025 00:00:00

Example 2: Truncating a Date to the Start of the Year with TRUNC

This example uses the 'YEAR' format model to find the very first day of the year for a given date.

Query:

SELECT 
  DATE '2025-10-27' AS "Original_Date",
  TRUNC(DATE '2025-10-27', 'YEAR') AS "Start_of_Year"
FROM DUAL;

Result:

Original_Date        Start_of_Year
-------------------- --------------------
27-OCT-2025 00:00:00 01-JAN-2025 00:00:00