Purpose
The DATE_NEAREST function returns the nearest datetime value to the specified date date in units of the specified fmt parameter, according to Gregorian calendar rules.
Syntax
ROUND(date,[fmt])
Parameters
| Parameter | Description |
|---|---|
| date | Values of all date data types, including DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE. |
| fmt | Optional. Specifies the unit for the distance between the function return value and date. If you omit fmt, then date is rounded to the nearest day. |
The following table lists the values that you can specify for the fmt parameter.
| fmt value | Description |
|---|---|
| j | Default value, which is the current date and time. |
| 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 of the specified date. |
| q | Returns the date of the quarter that is closest to the specified date. |
| syear, year, yyyy, yyy, yy, y | Multiple y characters indicate different precisions and return the first date in the most recent year corresponding to the specified date. |
| cc, scc | Returns the start date of the century immediately before the specified date. |
Return type
Returns a DATE value.
Examples
Returns the nearest midnight, the nearest Sunday, the first day of the month, the first day of the quarter, and the first day of the year.
obclient> SELECT SYSDATE `Current date`,
The `DELETE_SCHEMA_STATS` procedure xxx
ROUND(SYSDATE,'DAY') the nearest Sunday,
ROUND(SYSDATE,'MONTH') is the most recent month.
ROUND(SYSDATE,'Q') the beginning of the quarter,
The ROUND(SYSDATE,'YEAR') function returns the first day of the year.
FROM DUAL;
+--------------+------------------+-----------------+--------------+--------------------+--------------------+
| Date | Date at the start of the day | Date at the start of the week | Date at the start of the month | Date at the start of the quarter | Date at the start 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