• Skip to primary navigation
  • Skip to main content

Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / function / TRUNC Function in Oracle SQL – PLSQL

TRUNC Function in Oracle SQL – PLSQL

November 2, 2012 by techhoneyadmin

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


Filed Under: function Tagged With: how to use trunc function in oracle database query, how to use trunc function in oracle plsql, how to use trunc function in oracle sql, syntax and example of trunc function in oracle database query, syntax and example of trunc function in oracle plsql, syntax and example of trunc function in oracle sql, trunc function in oracle plsql, trunc function in oracle sql, TRUNCDATEPLSQL, using trunc function in oracle database query, using trunc function in oracle plsql, using trunc function in oracle sql

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in