Oracle ROUND (date) function

Last update on September 21 2024 10:31:57 (UTC/GMT +8 hours)

How to round date functions in Oracle?

The ROUND() function in Oracle is used to round a specified date to the nearest unit defined by a format model. It follows the rules of the Gregorian calendar, allowing users to manipulate dates effectively for various purposes.

Uses of Oracle ROUND(date) Function

Rounding Dates to Nearest Unit: Round a date to the nearest day, month, year, or other specified units.

Formatting Date Outputs: Control the output of dates for reporting or presentation by rounding them appropriately.

Simplifying Date Comparisons: Use rounding to make date comparisons easier by aligning them to a common unit.

Aggregating Date Data: Facilitate grouping of date-related data by rounding dates to relevant intervals in queries.

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