Purpose
The function returns the nearest date and time value to date, based on fmt as the unit of distance, following the Gregorian calendar rules.
Syntax
ROUND(date,[fmt])
Parameters
| Parameter | Description |
|---|---|
| date | The values of all date data types, including DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. |
| fmt | The unit of the difference between the returned value of the function and the date value. This is an optional parameter. If you do not specify fmt, then date is rounded to the nearest day. |
The following table lists the valid values of the fmt parameter (case insensitive).
| fmt | Description |
|---|---|
| j | Default: the date in the 0 hours of the most recent day. |
| day, dy, d | Returns the nearest Sunday to the specified date. |
| month, mon, mm, rm | Returns the date of the first day of the month closest to the specified date. |
| q | Returns the most recent quarter in the specified date. |
| syear, year, yyyy, yyy, yy, y | Multiple y characters indicate different levels of precision, returning the first date of the most recent year. |
| cc, scc | Returns the beginning of the century nearest to the specified date. |
Return type
Returns the DATE data type.
Examples
Returns the nearest zero date, Sunday, first of month, first of quarter, and first of year dates.
obclient> SELECT SYSDATE CURRENT DATE,
ROUND(SYSDATE) specifies the last date of the 0th hour,
ROUND(SYSDATE,'DAY') is Sunday,
ROUND(SYSDATE,'MONTH') returns the most recent month,
ROUND(SYSDATE,'Q') the start of the most recent quarter,
ROUND(SYSDATE,'YEAR') the first day of the last year.
FROM DUAL;
+--------------+------------------+-----------------+--------------+--------------------+--------------------+
| Current Date | 0:00 AM Date | Last Sunday | First Day of the Month | First Day of the Quarter | First Day of the Year |
+--------------+------------------+-----------------+--------------+--------------------+--------------------+
| 18-NOV-21 | 18-NOV-21 | 21-NOV-21 | 01-DEC-21 | 01-JAN-22 | 01-JAN-22 |
+--------------+------------------+-----------------+--------------+--------------------+--------------------+
1 row in set