In Oracle/PLSQL, the round function returns a date rounded to a specific unit of measure.
Syntax
The syntax for the round function is:
round( date, [ format ] )
date is the date to round.
format is the unit of measure to apply for rounding. If the format parameter is omitted, the round function will round to the nearest day.
Below are the valid format parameters:
| 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 |
Applies To
- Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
For Example
| round(to_date ('22-AUG-03'),'YEAR') | would return '01-JAN-04' |
| round(to_date ('22-AUG-03'),'Q') | would return '01-OCT-03' |
| round(to_date ('22-AUG-03'),'MONTH') | would return '01-SEP-03' |
| round(to_date ('22-AUG-03'),'DDD') | would return '22-AUG-03' |
| round(to_date ('22-AUG-03'),'DAY') | would return '24-AUG-03' |
