Oracle Plsql Round Function With Dates
Oracle / PLSQL: ROUND Function (with dates)
This Oracle tutorial explains how to use the Oracle/PLSQL ROUND function (as it applies to date values) with syntax and examples.
Description
The Oracle/PLSQL ROUND function returns a date rounded to a specific unit of measure.
ROUND Function Syntax (with dates)
The syntax for the ROUND function in Oracle/PLSQL is:
ROUND( date [, format] )Parameters or Arguments
- date
- format
The date to round.
Optional. The unit of measure to apply for rounding. If the format parameter is omitted, the ROUND function will round to the nearest day. It can be can one of the following values:
| Unit | Valid format parameters | Rounding Rule |
|---|---|---|
| Year | SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y | Rounds up on July 1st |
| ISO Year | IYYY, IY, I | |
| Quarter | Q | Rounds up on the 16th day of the second month of the quarter |
| Month | MONTH, MON, MM, RM | Rounds up on the 16th day of the month |
| Week | WW | Same day of the week as the first day of the year |
| IW | IW | Same day of the week as the first day of the ISO year |
| W | W | Same day of the week as the first day of the month |
| Day | DDD, DD, J | |
| Start day of the week | DAY, DY, D | |
| Hour | HH, HH12, HH24 | |
| Minute | MI |
Returns
The ROUND function (as it applies to dates) returns a date value.
Applies To
The ROUND function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Example - with dates
Let's look at some Oracle ROUND function examples and explore how to use the ROUND function in Oracle/PLSQL.
For example:
ROUND(TO_DATE ('22-AUG-03'),'YEAR')
Output: '01-JAN-04'
ROUND(TO_DATE ('22-AUG-03'),'Q')
Output: '01-OCT-03'
ROUND(TO_DATE ('22-AUG-03'),'MONTH')
Output: '01-SEP-03'
ROUND(TO_DATE ('22-AUG-03'),'DDD')
Output: '22-AUG-03'
ROUND(TO_DATE ('22-AUG-03'),'DAY')
Output: '24-AUG-03'