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