Purpose
You can this function to return the value of date rounded to the unit specified by fmt. It operates according to the rules of the Gregorian calendar.
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 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