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:
SELECT ROUND(TO_DATE ('22-AUG-12'),'YEAR')
FROM DUAL;
Will return 1/1/2013
Example 2:
SELECT ROUND(TO_DATE ('22-AUG-12'),'Q')
FROM DUAL;
Will return 10/1/2012
Example 3:
SELECT ROUND(TO_DATE ('22-AUG-12'),'MONTH')
FROM DUAL;
Will return 9/1/2012
Example 4:
SELECT ROUND(TO_DATE ('22-AUG-12'),'DDD')
FROM DUAL;
Will return 8/22/2012
Example 5:
SELECT ROUND(TO_DATE ('22-AUG-12'),'DAY')
FROM DUAL;
Will return 8/19/2012