Purpose
This function returns the nearest date and time value to the specified date date based on the unit specified by the fmt parameter. 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 TRUNC(date) must be the nearest date to date that is earlier than date, whereas the value returned by ROUND( ) can be the nearest date to date that is earlier than or later than date.
Syntax
TRUNC(date,[fmt])
Parameters
Parameter |
Description |
|---|---|
| date | A DATE value. |
| fmt | The unit for the distance between the return value of the function and date. |
The following table describes the values of the fmt parameter. The parameter is case-insensitive.
fmt Parameter Value |
Description |
|---|---|
| j | The default value. The nearest date to date at 0 o'clock. |
| day, dy, or d | The nearest Sunday to the specified date. |
| month, mon, mm, or rm | The nearest date to the specified date that is the first day of the month. |
| q | The nearest date to the specified date that is the first day of the quarter. |
| yyyy, yyy, yy, or y | The number of y characters indicates the precision. The nearest date to the specified date that is the first day of the year. |
| cc or scc | The nearest date to the specified date that is the first day of the century. |
Return type
DATE
Examples
Use the TRUNC function to calculate the nearest date that meets the requirements from the current time.
obclient> SELECT SYSDATE AS "Current Date",
TRUNC(SYSDATE) AS "Today's Date",
TRUNC(SYSDATE,'DAY') AS "This Week's Sunday",
TRUNC(SYSDATE,'MONTH') AS "This Month's First Day",
TRUNC(SYSDATE,'Q') AS "This Quarter's First Day",
TRUNC(SYSDATE,'YEAR') AS "This Year's First Day" FROM DUAL;
+--------------+--------------+-----------------+-----------+-----------------+-----------------+
| Current Date | Today's Date | This Week's Sunday | This Month's First Day | This Quarter's First Day | This Year's First Day |
+--------------+--------------+-----------------+-----------+-----------------+-----------------+
| 18-NOV-21 | 18-NOV-21 | 14-NOV-21 | 01-NOV-21 | 01-OCT-21 | 01-JAN-21 |
+--------------+--------------+-----------------+-----------+-----------------+-----------------+
1 row in set