TRUNC Function in Oracle SQL – PLSQL

The TRUNC function in Oracle SQL / PLSQL is used to return the date truncated to a specific instance.

Syntax for the TRUNC function in Oracle SQL / PLSQL is:

SELECT TRUNC(date,[format])
FROM table_name;

Here in the above statement:

  • ‘date’ is the date that is passed to be truncated
  • ‘format’ is the unit up to which the rounding is to be done. If format is not given or omitted then the date is truncated to the nearest day. Any hours, minutes and seconds will be truncated off.

Considering sysdate as November 2nd 2012

Observe the table below to understand what TRUNC function does when passed with different format parameters.

Parameter_Passed Explanation Select_Query Output
YEAR Truncates to nearest year
SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL;
1/1/2012
YYYY Truncates to nearest year
SELECT TRUNC(SYSDATE,'YYYY') FROM DUAL;
1/1/2012
YYY Truncates to nearest year
SELECT TRUNC(SYSDATE,'YYY') FROM DUAL;
1/1/2012
YY Truncates to nearest year
SELECT TRUNC(SYSDATE,'YY') FROM DUAL;
1/1/2012
Y Truncates to nearest year
SELECT TRUNC(SYSDATE,'Y') FROM DUAL;
1/1/2012
SYYYY Truncates to nearest year
SELECT TRUNC(SYSDATE,'SYYYY') FROM DUAL;
1/1/2012
SYEAR Truncates to nearest year
SELECT TRUNC(SYSDATE,'SYEAR') FROM DUAL;
1/1/2012
IYYY Truncates to nearest year based on ISO standard
SELECT TRUNC(SYSDATE,'IYYY') FROM DUAL;
1/2/2012
IYY Truncates to nearest year based on ISO standard
SELECT TRUNC(SYSDATE,'IYY') FROM DUAL;
1/2/2012
IY Truncates to nearest year based on ISO standard
SELECT TRUNC(SYSDATE,'IY') FROM DUAL;
1/2/2012
I Truncates to nearest year based on ISO standard
SELECT TRUNC(SYSDATE,'I') FROM DUAL;
1/2/2012
Q TRUNC to the nearest Quarter of Year (1,2,3,4) January-March 1st quarter
SELECT TRUNC(SYSDATE,'Q') FROM DUAL;
10/1/2012
MM Truncates to the 16th day of Month
SELECT TRUNC(SYSDATE,'MM') FROM DUAL;
11/1/2012
MON Truncates to the 16th day of Month
SELECT TRUNC(SYSDATE,'MON') FROM DUAL;
11/1/2012
MONTH Truncates to the 16th day of Month
SELECT TRUNC(SYSDATE,'MONTH') FROM DUAL;
11/1/2012
RM Truncates to the 16th day of Month
SELECT TRUNC(SYSDATE,'RM') FROM DUAL;
11/1/2012
WW Same day of the week as 1st day of year
SELECT TRUNC(SYSDATE,'WW') FROM DUAL;
10/28/2012
W Same day of the week as 1st day of month
SELECT TRUNC(SYSDATE,'W') FROM DUAL;
11/1/2012
IW Same day of the week as 1st day of ISO year
SELECT TRUNC(SYSDATE,'IW') FROM DUAL;
10/29/2012
D Starting Day of Week
SELECT TRUNC(SYSDATE,'D') FROM DUAL;
10/28/2012
DY Starting Day of Week
SELECT TRUNC(SYSDATE,'DY') FROM DUAL;
10/28/2012
DAY Starting Day of Week
SELECT TRUNC(SYSDATE,'DAY') FROM DUAL;
10/28/2012
DD Nearest Day
SELECT TRUNC(SYSDATE,'DD') FROM DUAL;
11/2/2012
DDD Nearest Day
SELECT TRUNC(SYSDATE,'DDD') FROM DUAL;
11/2/2012
J Nearest Day
SELECT TRUNC(SYSDATE,'J') FROM DUAL;
11/2/2012
HH Hour of Day (1-12)
SELECT TRUNC(SYSDATE,'HH') FROM DUAL;
11/2/2012 4:00:00 PM
HH12 Hour of Day (1-12)
SELECT TRUNC(SYSDATE,'HH12') FROM DUAL;
11/2/2012 4:00:00 PM
HH24 Hour of Day (0-23)
SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;
11/2/2012 4:00:00 PM
MI Minute (0-59)
SELECT TRUNC(SYSDATE,'MI') FROM DUAL;
11/2/2012 4:47:00 PM

Example 1:

SELECT TRUNC(TO_DATE ('22-AUG-12'),'YEAR')
FROM DUAL;

Will return 1/1/2012


Example 2:

SELECT TRUNC(TO_DATE ('22-AUG-12'),'Q')
FROM DUAL;

Will return 7/1/2012


Example 3:

SELECT TRUNC(TO_DATE ('22-AUG-12'),'MONTH')
FROM DUAL;

Will return 8/1/2012


Example 4:

SELECT TRUNC(TO_DATE ('22-AUG-12'),'DDD')
FROM DUAL;

Will return 8/22/2012


Example 5:

SELECT TRUNC(TO_DATE ('22-AUG-12'),'DAY')
FROM DUAL;

Will return 8/19/2012


Tagged , , , , , , , , , , , . Bookmark the permalink.