Oracle TRUNC(DATE) Function

Oracle TRUNC(date) is a built-in function that truncates a given date to the specified unit.

Oracle TRUNC(date) Syntax

Here is the syntax of the Oracle TRUNC(date) function:

TRUNC(date)
TRUNC(date, fmt)

Parameters

date

Required. The date/datetime expression to be truncated. It must be a value of the DATE data type.

fmt

Optional. It specifies the unit to truncate to. If this parameter is not specified, date will be truncated to the day (DD).

The following table summarizes the values that fmt can take:

Format Model Unit
CC, SCC Century.
SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y Year.
IYYY, IYY, IY, I Year that includes the ISO 8601 standard definition of the calendar week.
Q Quarter.
MONTH, MON, MM, RM Month.
WW Week of the year that starts with the first day of the year.
IW Week of the year that starts with Monday of the first week of the year according to the ISO 8601 standard.
W Week of the month that starts with the first day of the month.
DDD, DD, J Day.
DAY, DY, D The start day of the week.
HH, HH12, HH24 Hour.
MI Minute.

Return Value

The Oracle TRUNC(date) function returns a truncated datetime value. The return value is always of the DATE data type.

If any argument is NULL, TRUNC(date) returns NULL.

Oracle TRUNC(date) Examples

Here are some examples that demonstrate how to use the Oracle TRUNC(date) function.

Basic Usage

The following statement truncates 2023-02-12 11:59:59 according to the default unit:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT
  TRUNC(TO_DATE('2023-02-12 11:59:59', 'YYYY-MM-DD HH24:MI:SS'))
FROM dual;

Output:

TRUNC(TO_DATE('2023-02-1211:59:59','YYYY-MM-DDHH24:MI:SS'))
______________________________________________________________
2023-02-12

Here, since we did not specify the fmt parameter, 2023-02-12 11:59:59 is truncated to the day.

Truncating to Year

The Oracle TRUNC(date) function allows you to truncate a datetime to the year by using the YEAR unit:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT
  TRUNC(DATE '2023-06-30', 'YEAR') "2023-06-30",
  TRUNC(DATE '2023-07-01', 'YEAR') "2023-07-01"
FROM dual;

Output:

2023-06-30    2023-07-01
_____________ _____________
2023-01-01    2023-01-01

Month

The Oracle TRUNC(date) function allows you to truncate a date/time to the month using the MONTH unit:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT
  TRUNC(DATE '2023-02-15', 'MONTH') "2023-02-15",
  TRUNC(DATE '2023-02-16', 'MONTH') "2023-02-16"
FROM dual;

Output:

2023-02-15    2023-02-16
_____________ _____________
2023-02-01    2023-02-01

TRUNC(date) vs ROUND(date)

TRUNC(date) is used to truncate a date, while ROUND(date) is used to round a date. The following example illustrates this:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT
  TRUNC(DATE '2023-07-01', 'YEAR') "TRUNC 2023-07-01",
  ROUND(DATE '2023-07-01', 'YEAR') "ROUND 2023-07-01"
FROM dual;

Output:

TRUNC 2023-07-01    ROUND 2023-07-01
___________________ ___________________
2023-01-01          2024-01-01

NULL parameter

If either parameter is NULL, TRUNC(date) will return NULL.

SET NULL 'NULL';
SELECT
    TRUNC(NULL, 'YEAR') NULL_1,
    TRUNC(DATE '2023-02-15', NULL) NULL_2,
    TRUNC(NULL, NULL) NULL_3
FROM dual;

Output:

   NULL_1 NULL_2       NULL_3
_________ _________ _________
     NULL NULL           NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

The Oracle TRUNC(date) is a built-in function that truncates a given date to a specified unit.