Purpose
ROUND(date) returns the value of date rounded to the unit specified by fmt. The rules of the Gregorian calendar apply in the calculation.
Syntax
ROUND(date,[fmt])
Parameters
Parameter |
Description |
|---|---|
| date | A value of any data type that contains date information, such as DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE. |
| fmt | The unit of time between the return value and the value of date. This parameter is optional. If you do not specify fmt, the value of date is rounded to the nearest day. |
The following table lists available values for fmt. The values are case-insensitive.
Value |
Description |
|---|---|
| j | Rounds to the date of the nearest 0 o'clock. This is the default value. |
| day, dy, d | Rounds to the nearest Sunday. |
| month, mon, mm, rm | Rounds to the first date of the nearest month. |
| q | Rounds to the first date of the nearest quarter. |
| syear, year, yyyy, yyy, yy, y | Rounds to the first date of the nearest year with the number of digits specified by the number of instances of y. |
| cc, scc | Rounds to the first date of the nearest century. |
Return type
The return type is DATE.
Examples
The following example returns the date of the nearest 0 o'clock, the date of the nearest Sunday, the first date of the nearest month, the first date of the nearest quarter, and the first date of the nearest year.
obclient> SELECT SYSDATE "Current date",
ROUND(SYSDATE) "Date of the nearest 0 o'clock",
ROUND(SYSDATE,'DAY') "Nearest Sunday",
ROUND(SYSDATE,'MONTH') "First date of the nearest month",
ROUND(SYSDATE,'Q') "First date of the nearest quarter",
ROUND(SYSDATE,'YEAR') "First date of the nearest year"
FROM DUAL;
+--------------+-------------------------------+----------------+---------------------------------+-----------------------------------+--------------------------------+
| Current date | Date of the nearest 0 o'clock | Nearest Sunday | First date of the nearest month | First date of the nearest quarter | First date of the nearest year |
+--------------+-------------------------------+----------------+---------------------------------+-----------------------------------+--------------------------------+
| 18-NOV-21 | 18-NOV-21 | 21-NOV-21 | 01-DEC-21 | 01-JAN-22 | 01-JAN-22 |
+--------------+-------------------------------+----------------+---------------------------------+-----------------------------------+--------------------------------+
1 row in set