Purpose
This function returns the nearest date-time value from the specified date date in the specified unit, calculated by rules of the Gregorian calendar.
Syntax
ROUND(date,[fmt])
Parameters
Parameters |
Description |
|---|---|
| date | The value of any date data type, including DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. |
| fmt | Optional. The units of the function's return value from date. If fmt is omitted, the value of date is rounded to the nearest day. |
The following table lists the allowed values of the fmt parameter (case-insensitive).
fmt Value |
Description |
|---|---|
| j | Default value: the date at the 00:00 hour of the previous day. |
| DAY, DY, D | Returns the Sunday closest to the specified date. |
| month, mon, mm, rm | Returns the date of the first day of the month nearest to the specified date. |
| q | Returns the date for the most recent quarter closest to the specified date. |
| syear, year, yyyy, yyy, yy, y | The number of y's indicates the degree of precision; it returns the first date in the specified year. |
| cc, scc | Returns the date of the start of the century closest to the specified date. |
Return type
Returns a value of the DATE data type.
Examples
Returns the nearest date to the current date, midnight, Sunday, the beginning of the month, beginning of the quarter, and beginning of the year.
obclient> SELECT SYSDATE,
ROUND(SYSDATE) to most recent date with hour zero.
ROUND(SYSDATE,'DAY') is the most recent Sunday,
ROUND(SYSDATE,'MONTH') the nearest beginning of the month,
ROUND(SYSDATE,'Q') is the first day of the previous quarter,
ROUND(SYSDATE,'YEAR') - most recent beginning of a year
FROM DUAL;
+--------------+------------------+-----------------+--------------+--------------------+--------------------+
| date | last midnight | last Sunday | start of month | start of quarter | start of year |
+--------------+------------------+-----------------+--------------+--------------------+--------------------+
| 18-NOV-21 | 18-NOV-21 | 21-NOV-21 | 01-DEC-21 | 01-JAN-22 | 01-JAN-22 |
+--------------+------------------+-----------------+--------------+--------------------+--------------------+
1 row in set