The TRUNC function returns a date that is truncated according to a specified template pattern. If the template pattern is omitted, the date is truncated to the nearest day. The following table shows the template patterns that can be used for the TRUNC function.

Table 1. Template date patterns for the TRUNC function
Pattern Description
CC, SCC Returns January 1, cc01 where cc is the first 2 digits of the given year.
SYYY, YYYY, YEAR, SYEAR, YYY, YY, Y Returns January 1, yyyy where yyyy is the given year.
IYYY, IYY, IY, I Returns the start date of the ISO year containing the given date.
Q Returns the first day of the quarter containing the given date.
MONTH, MON, MM, RM Returns the first day of the specified month.
WW Returns the largest date just prior to, or the same as the given date that corresponds to the same day of the week as the first day of the year.
IW Returns the start of the ISO week containing the given date.
W Returns the largest date just prior to, or the same as the given date that corresponds to the same day of the week as the first day of the month.
DDD, DD, J Returns the start of the day for the given date.
DAY, DY, D Returns the start of the week (Sunday) containing the given date.
HH, HH12, HH24 Returns the start of the hour.
MI Returns the start of the minute.

Examples

The following example truncates the date to the hundred years unit.

SELECT TO_CHAR(TRUNC(TO_DATE('1951','YYYY'),'CC'),'DD-MON-YYYY') "Century" FROM DUAL;

   Century
-------------
 01-JAN-1901
(1 row)

The following example truncates the date to the year.

SELECT TO_CHAR(TRUNC(TO_DATE('01-JUL-1999','DD-MON-YYYY'),'Y'),'DD-MON-YYYY') "Year" FROM DUAL;

    Year
-------------
 01-JAN-1999
(1 row)

The following example truncates the date to the beginning of the ISO year.

SELECT TO_CHAR(TRUNC(TO_DATE('01-JUL-2004','DD-MON-YYYY'),'IYYY'),'DD-MON-YYYY') "ISO Year" FROM DUAL;

  ISO Year
-------------
 29-DEC-2003
(1 row)

The following example truncates the date to the start date of the quarter.

SELECT TRUNC(TO_DATE('16-FEB-07','DD-MON-YY'),'Q') "Quarter" FROM DUAL;

      Quarter
--------------------
 01-JAN-07 00:00:00
(1 row)

The following example truncates the date to the start date of the month.

SELECT TRUNC(TO_DATE('16-DEC-07','DD-MON-YY'),'MONTH') "Month" FROM DUAL;

       Month
--------------------
 01-DEC-07 00:00:00
(1 row)

The following example truncates the date to the start of the week determined by the first day of the year. For example, the first day of 2007 is a Monday, so the first Monday before January 19 is January 15. 

SELECT TRUNC(TO_DATE('19-JAN-07','DD-MON-YY'),'WW') "Week" FROM DUAL;

        Week
--------------------
 15-JAN-07 00:00:00
(1 row)

The following example truncates the date to the beginning of an ISO week. An ISO week starts on a Monday. January 2, 2004 is within the ISO week that starts on Monday, December 29, 2003. 

SELECT TRUNC(TO_DATE('02-JAN-04','DD-MON-YY'),'IW') "ISO Week" FROM DUAL;

      ISO Week
--------------------
 29-DEC-03 00:00:00
(1 row)

The following example truncates the date to the start of the week where a week is considered to start on the same day as the first day of the month. 

SELECT TRUNC(TO_DATE('21-MAR-07','DD-MON-YY'),'W') "Week" FROM DUAL;

        Week
--------------------
 15-MAR-07 00:00:00
(1 row)

The following example truncates the date to the start of the day.

SELECT TRUNC(TO_DATE('04-AUG-07 12:00:00 PM','DD-MON-YY HH:MI:SS AM'),'J') "Day" FROM DUAL;

        Day
--------------------
 04-AUG-07 00:00:00
(1 row)

The following example truncates the date to the start of the week (Sunday).

SELECT TRUNC(TO_DATE('09-AUG-07','DD-MON-YY'),'DAY') "Day of Week" FROM DUAL;

    Day of Week
--------------------
 05-AUG-07 00:00:00
(1 row)

The following example truncates the date to the start of the hour.

SELECT TO_CHAR(TRUNC(TO_DATE('09-AUG-07 08:30','DD-MON-YY HH:MI'),'HH'),'DD-MON-YY HH24:MI:SS') "Hour" FROM DUAL;

        Hour
--------------------
 09-AUG-07 08:00:00
(1 row)

The following example truncates the date to the start of the minute.

SELECT TO_CHAR(TRUNC(TO_DATE('09-AUG-07 08:30:30','DD-MON-YY HH:MI:SS'),'MI'),'DD-MON-YY HH24:MI:SS') "Minute" FROM DUAL;

       Minute
--------------------
 09-AUG-07 08:30:00
(1 row)