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