The Oracle ROUND (Date) function rounds the input date data to and returns a DATE as per the round_format. It basically returns the nearest date as per the period in the round_format. It computes as per the Gregorian calendar.
Let us understand the syntax of Oracle round date, its input parameters, return types and examples in this Oracle Tutorial.
This page contains (Skip to topic)..
Oracle ROUND (Date) syntax
ROUND( date [, round_format] )
Oracle ROUND (Date) returns
The Oracle ROUND function rounds the date and returns a DATE datatype.
Oracle ROUND (Date) Input Arguments Or Parameters
- The date parameter is of a date datatype or expression which can yield DATE.
- The round_format tells Oracle to round to the nearest day, month, quarter or year etc.
Oracle ROUND (Date) More On round_format
- If round_format is omitted, then Oracle truncates to the nearest day.
- Oracle rounds to the left or right of input date data based on the round_formatand returns the beginning of the period or the end of that period. This is slightly different than the Oracle TRUNC (Date) function.
Oracle ROUND (Date) Practical Examples
select round(to_date('27-OCT-08'), 'YEAR') "Nearest New Year" from dual; Nearest New Year ---------------- 01-01-09
As you can see, since 1st Jan 2009 is the nearest end of the year for 27th Oct 2008, the round function returned 01-01-09.
Oracle ROUND (Date) function round_format model
|One greater than the first two digits of a four-digit year|
|Year (rounds up on July 1)|
|Q||Quarter (rounds up on the sixteenth day of the second month of the quarter)|
|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|
Based on the format, it rounds up to the closest rounding unit. This table also applies to the oracle truncate function.