Oracle ROUND (date) function
How to round date functions in Oracle?
The ROUND() function is used to get the date rounded to the unit specified by the format model. It operates according to the rules of the Gregorian calendar.
Syntax:
ROUND(date [, fmt ])
Parameters:
Name | Description |
---|---|
date | The specified date. |
fmt(Optional) | The unit of measure to apply for rounding. If the parameter is not present, then a date is rounded to the nearest day. |
Return Value Type:
The value returned is always of data type DATE.
Date format models for the ROUND function:
Format Model | Rounding Unit |
---|---|
CC SCC | One greater than the first two digits of a four-digit year |
SYYYY YYYY YEAR SYEAR YYY YY Y | Year (rounds up on July 1) |
IYYY IY IY I | ISO Year |
Q | Quarter (rounds up on the sixteenth day of the second month of the quarter) |
MONTH MON MM RM | Month (rounds up on the sixteenth day) |
WW | Same day of the week as the first day of the year |
IW | Same day of the week as the first day of the ISO year |
W | Same day of the week as the first day of the month |
DDD DD J | Day |
DAY DY D | Starting day of the week |
HH HH12 HH24 | Hour |
MI | Minute |
Applies to:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
Pictorial Presentation

Examples: Oracle ROUND(date) function
The following example rounds a date to the first day of the following month and year :
SQL> SELECT ROUND(TO_DATE ('16-SEP-2015'),'MONTH') "New Month",
2 ROUND(TO_DATE ('16-SEP-2015'),'YEAR') "New Year"
3 FROM DUAL;
Sample Output:
New Month New Year ------------ -------------------- 01-OCT-2015 01-JAN-2016
Previous:
NUMTOYMINTERVAL
Next:
SESSIONTIMEZONE
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join