Purpose
The ROUND_TO function returns a date-time value closest to the specified date date that is specified by the format fmt and is earlier than date.
Note
Unlike the value returned by the ROUND( ) function, the value returned by the TRUNC(date) function must be the nearest date to date that is before the current date.
Syntax
TRUNC(date,[fmt])
Parameters
| Parameter | Description | --- | --- | ||||
|---|---|---|---|---|---|---|---|
| date | The DATE data type. |
||||||
| fmt | Specifies the unit of distance between the function's return value and the date. |
The following table lists the values of the fmt parameter, which is not case-sensitive.
| Value of the fmt parameter | Description |
|---|---|
| j | The default is the current date. |
| day、dy、d | Returns the most recent Sunday from a given date. |
| month, mon, mm, rm | Returns the date of the first day of the next month. |
| q | Returns the nearest quarter to the specified date. |
| yyyy, yyy, yy, or y | Each y corresponds to a different level of precision, returning the first day of the nearest year to the specified date. |
| cc, scc | Returns the first date of the century nearest to the specified date. |
Return type
The procedure returns a DATE value.
Examples
Use the TRUNC function to compute the date value closest to the current time.
obclient> SELECT SYSDATE 'Current Date',
TRUNC(SYSDATE) Today's date,
TRUNC(SYSDATE,'DAY'),
TRUNC(SYSDATE,'MONTH') The first day of this month,
TRUNC(SYSDATE,'Q') is the first day of the quarter,
TRUNC(SYSDATE,'YEAR') gives the first day of the current year from DUAL.
+--------------+--------------+-----------------+-----------+-----------------+-----------------+
| Date | Today's date | This Sunday | This month's first day | This quarter's start date | This year's start date |
+--------------+--------------+-----------------+-----------+-----------------+-----------------+
| 18-NOV-21 | 18-NOV-21 | 14-NOV-21 | 01-NOV-21 | 01-OCT-21 | 01-JAN-21 |
+--------------+--------------+-----------------+-----------+-----------------+-----------------+
1 row in set