Oracle/PLSQL: Round Function (with dates)
In Oracle/PLSQL, the round function returns a date rounded to a specific unit of measure.
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 8i, Oracle 9i, Oracle 10g, Oracle 11g
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'