Purpose
This function returns a date and time value that is nearest to the specified date value date, in the units specified by fmt, and which is before the date value.
Important
Dates returned by the TRUNC(date) function must be closest dates that occur before the date specified in the date parameter. ROUND( ) can be the nearest date that occurs before or after the date specified in the date parameter.
Syntax
TRUNC(date,[fmt])
Parameters
| Parameter | Description |
|---|---|
| date | The DATE data type. |
| fmt | Specifies the unit of the function return value and the date unit. |
The following table lists the values of the fmt parameter, which is not case-sensitive.
| fmt parameter value | Description |
|---|---|
| j | The default value for j is the current date, which is 0 days before the current date. |
| day、dy、d | Returns the most recent Sunday before or at the specified date. |
| month, mon, mm, rm | Returns the date of the first day of the month nearest to the specified date. |
| q | Returns the most recent quarter date relative to the specified date. |
| yyyy, yyy, yy, or y | Multiple y values indicate different precisions, and it returns the first date of the year closest to the specified date. |
| cc, scc | The century start date of the most recent century before the specified date. |
Return type
Returns a value of the DATE data type.
Examples
Use TRUNC to compute the most recent date value that satisfies the conditions.
obclient> SELECT SYSDATE AS the_current_date,
TRUNC(SYSDATE) current date,
TRUNC(SYSDATE,'DAY') specifies Sunday of the current week.
TRUNC(SYSDATE,'MONTH') first day of this month,
TRUNC(SYSDATE,'Q') beginning of the quarter date,
TRUNC(SYSDATE,'YEAR') for this year's starting date FROM DUAL;
+--------------+--------------+-----------------+-----------+-----------------+-----------------+
| Current date | Today's date | This week's Sunday | This month's start | 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