Tech Honey

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

You are here: Home / Oracle / function / ROUND Function in Oracle SQL – PLSQL

ROUND Function in Oracle SQL – PLSQL

by

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

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

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

Here in the above statement:

  • ‘date’ is the date that is passed to be rounded
  • ‘format’ is the unit up to which the rounding is to be done. If format is not given or omitted then the date is rounded to the nearest day.

Considering sysdate as November 2nd 2012
Observe the table below to understand what ROUND function does when passed with different format parameters:

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

Example1:

view source
print?
1SELECT ROUND(TO_DATE ('22-AUG-12'),'YEAR')
2FROM DUAL;

Will return 1/1/2013

Example 2:

view source
print?
1SELECT ROUND(TO_DATE ('22-AUG-12'),'Q')
2FROM DUAL;

Will return 10/1/2012

Example 3:

view source
print?
1SELECT ROUND(TO_DATE ('22-AUG-12'),'MONTH')
2FROM DUAL;

Will return 9/1/2012

Example 4:

view source
print?
1SELECT ROUND(TO_DATE ('22-AUG-12'),'DDD')
2FROM DUAL;

Will return 8/22/2012

Example 5:

view source
print?
1SELECT ROUND(TO_DATE ('22-AUG-12'),'DAY')
2FROM DUAL;

Will return 8/19/2012