All Products
Search
Document Center

TRUNC (date)

Last Updated: Jun 18, 2021

The TRUNC function truncates an input value based on the specified unit. This function returns a datetime value that indicates the nearest date and time to the specified date based on the specified interval requirement. The fmt parameter specifies the unit for the time interval between the return value and the specified date. The returned date occurs before the date that is specified by date.

Notice

TRUNC is different from ROUND in the following aspect: The TRUNC function returns the nearest date to the specified date and the nearest date occurs before the specified date. ROUND returns the nearest date to the specified date and the nearest date occurs before or after the specified date.

Syntax

TRUNC (date,[fmt])

Parameters

Parameter

Description

date

The DATE data type.

fmt

The unit for the time interval between the return value of the function and the specified date. The following table lists the valid values of this parameter. The values of this parameter are not case-sensitive.

fmt parameter

Description

j

The default value. The function returns the nearest date to 00:00.

day, dy, or d

Returns the nearest Sunday to the specified date.

month, mon, mm, or rm

Returns the first date of the nearest month to the specified date.

q

Returns the first date of the nearest quarter to the specified date.

yyyy, yyy, yy, or y

Returns the first date of the nearest year to the specified date. yyyy, yyy, yy, and y correspond to different precisions.

cc or scc

Returns the first date of the nearest century to the specified date.

Return type

DATE data type

Examples

In the following example, the TRUNC function calculates the nearest dates to the SYSDATE based on the specified requirements:

SELECT SYSDATE The current date,
TRUNC(SYSDATE) The date of today,
TRUNC(SYSDATE,'DAY') The Sunday of this week,
TRUNC(SYSDATE,'MONTH') The first date of this month,
TRUNC(SYSDATE,'Q') The first date of this quarter,
TRUNC(SYSDATE,'YEAR') The first date of this year FROM DUAL;

The following query result is returned:

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| The current date            | The date of today             | The Sunday of this week          | The first date of this month              | The first date of this quarter           | The first date of this year          |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2020-03-08 22:41:46 | 2020-03-08 00:00:00 | 2020-03-08 00:00:00 | 2020-03-01 00:00:00 | 2020-01-01 00:00:00 | 2020-01-01 00:00:00 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

In the following example, ROUND calculates the nearest dates that meet the different specified requirements when the input date is the same:

SELECT SYSDATE The current date,
ROUND(SYSDATE) The nearest date to 00:00,
ROUND(SYSDATE,'DAY') The nearest Sunday,
ROUND(SYSDATE,'MONTH') The first date of the nearest month,
ROUND(SYSDATE,'Q') The first date of the nearest quarter,
ROUND(SYSDATE,'YEAR') The first date of the nearest year FROM DUAL;

The following query result is returned:

+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| The current date            | The nearest date to 00:00          | The nearest Sunday          | The first date of the nearest month            | The first date of the nearest quarter         | The first date of the nearest year        |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2020-03-08 22:41:02 | 2020-03-09 00:00:00 | 2020-03-08 00:00:00 | 2020-03-01 00:00:00 | 2020-04-01 00:00:00 | 2020-01-01 00:00:00 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+