Purpose
This function returns the nearest date value to the specified date date in the unit specified by the fmt parameter, and the returned date value is earlier than date.
Notice
The difference between the return value of the ROUND( ) function and the return value of the TRUNC(date) function is that the value returned by the TRUNC(date) function must be the nearest date to the date value earlier than the date value, while the value returned by the ROUND( ) function can be the nearest date to the date value earlier than or later than the date value.
Syntax
TRUNC(date,[fmt])
Parameters
| Parameter | Description |
|---|---|
| date | A DATE value. |
| fmt | The unit of the distance between the return value of the function and the date value. |
The following table describes the values of the fmt parameter. The parameter is not case-sensitive.
| fmt Parameter Value | Description |
|---|---|
| j | The default value, which indicates the nearest 0 o'clock date. |
| day, dy, and d | The nearest Sunday to the specified date. |
| month, mon, mm, and rm | The nearest date of the first day of the month to the specified date. |
| q | The nearest date of the first day of the quarter to the specified date. |
| yyyy, yyy, yy, and y | Multiple y values indicate different precisions. The nearest date of the first day of the year to the specified date. |
| cc and scc | The nearest date of the beginning of the century to the specified date. |
Return type
DATE
Examples
Use the TRUNC function to calculate the nearest date value that meets the requirements from the current time.
obclient> SELECT SYSDATE AS "Current date",
TRUNC(SYSDATE) AS "Today",
TRUNC(SYSDATE,'DAY') AS "This Sunday",
TRUNC(SYSDATE,'MONTH') AS "First day of this month",
TRUNC(SYSDATE,'Q') AS "First day of this quarter",
TRUNC(SYSDATE,'YEAR') AS "First day of this year" FROM DUAL;
+--------------+--------------+-----------------+-----------+-----------------+-----------------+
| Current date | Today | This Sunday | First day of this month | First day of this quarter | First day of this year |
+--------------+--------------+-----------------+-----------+-----------------+-----------------+
| 18-NOV-21 | 18-NOV-21 | 14-NOV-21 | 01-NOV-21 | 01-OCT-21 | 01-JAN-21 |
+--------------+--------------+-----------------+-----------+-----------------+-----------------+
1 row in set