Purpose
This function returns the nearest date and time value to the specified date date, based on the Gregorian calendar, at the unit specified by the fmt parameter.
Syntax
ROUND(date[,fmt])
Parameters
| Parameter | Description |
|---|---|
| date | A value of any date data type, including DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. |
| fmt | The unit for the distance between the returned value and date. This parameter is optional. If you omit fmt, date is rounded to the nearest day. |
The following table lists the valid values of the fmt parameter (case-insensitive).
| fmt Value | Description |
|---|---|
| j | Default value. Returns the date at the beginning of the day. |
| day, dy, d | Returns the nearest Sunday to the specified date. |
| month, mon, mm, rm | Returns the date at the beginning of the nearest month to the specified date. |
| q | Returns the date at the beginning of the nearest quarter to the specified date. |
| syear, year, yyyy, yyy, yy, y | The number of y indicates the precision. Returns the date at the beginning of the nearest year to the specified date. |
| cc, scc | Returns the date at the beginning of the nearest century to the specified date. |
Return type
DATE
Examples
Return the nearest date and time values to the current date, including the beginning of the day, the nearest Sunday, the beginning of the nearest month, the beginning of the nearest quarter, and the beginning of the nearest year.
obclient> SELECT SYSDATE AS "Current Date",
ROUND(SYSDATE) AS "Nearest 00:00:00",
ROUND(SYSDATE,'DAY') AS "Nearest Sunday",
ROUND(SYSDATE,'MONTH') AS "Nearest Month",
ROUND(SYSDATE,'Q') AS "Nearest Quarter",
ROUND(SYSDATE,'YEAR') AS "Nearest Year"
FROM DUAL;
+--------------+------------------+-----------------+--------------+--------------------+--------------------+
| Current Date | Nearest 00:00:00 | Nearest Sunday | Nearest Month| Nearest Quarter | Nearest Year |
+--------------+------------------+-----------------+--------------+--------------------+--------------------+
| 18-NOV-21 | 18-NOV-21 | 21-NOV-21 | 01-DEC-21 | 01-JAN-22 | 01-JAN-22 |
+--------------+------------------+-----------------+--------------+--------------------+--------------------+
1 row in set