Purpose
TRUNC(date) returns the value of date truncated to the nearest date in the unit specified by fmt. In addition, the return value must be earlier than the value of date.
Notice
The difference between the return values of this function and ROUND() is that TRUNC(date) returns the nearest date that must be earlier than the value of date, while ROUND() returns the nearest date that can be earlier or later than the value of date.
Syntax
TRUNC(date,[fmt])
Parameters
| Parameter | Description |
|---|---|
| date | A value of the DATE data type. |
| fmt | The unit of time between the return value and the value of date. |
The following table lists the available values for fmt. The values are case-insensitive.
| Value | Description |
|---|---|
| j | Rounds to the date of the nearest 0 o'clock. This is the default value. |
| day, dy, d | Rounds to the nearest Sunday. |
| month, mon, mm, rm | Rounds to the first date of the nearest month. |
| q | Rounds to the first date of the nearest quarter. |
| yyyy, yyy, yy, y | Rounds to the first date of the nearest year with the number of digits specified by the number of instances of y. |
| cc, scc | Rounds to the first date of the nearest century. |
Return type
The return type is DATE.
Examples
The following example uses the TRUNC function to calculate the desired dates nearest to the current time.
obclient> SELECT SYSDATE Current date,
TRUNC(SYSDATE) Date of today,
TRUNC(SYSDATE,'DAY') This Sunday,
TRUNC(SYSDATE,'MONTH') First date of this month,
TRUNC(SYSDATE,'Q') First date of this quarter,
TRUNC(SYSDATE,'YEAR') First date of this year FROM DUAL;
+--------------+--------------+-----------------+-----------+-----------------+-----------------+
| Current date | Date of today | This Sunday | First date of this month | First date of this quarter | First date 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
