Sign inTry Free

TRUNC

On this page

Truncates a date using the specified format.

Returns a truncated date. If format is not provided, the date is truncated to the nearest day.

Syntax

TRUNC(DATE [,format])

Arguments

  • date: Can be any valid date format, including: DATE, TIMESTAMP, TIMESTAMP(6), DATETIME, DATETIME(6)

  • format: The precision of the truncated date, as described in the table below:

Valid Format Parameters

Description

SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y

Returns the first day of the year

IYYY, IY, I

Returns the first day of the ISO year

Q

Returns the first day of the quarter

MONTH, MON, MM, RM

Returns the first day of the month

WW

Returns the same day of the week as the first day of the year

IW

Returns the same day of the week as the first day of the calendar week as defined by the ISO 8601 standard, which is Monday

W

Returns the same day of the week as the first day of the month

DDD, DD, J

DAY, DY, D

Returns the starting day of the week, which is Sunday for non-ISO standard

HH, HH12, HH24

Returns the date with a truncated hour

MI

Returns the date with a truncated minute

Return Type

DATE

Examples

SELECT * FROM tr_date_tests;
+------------+
| datey      |
+------------+
| 2019-09-10 |
+------------+

YEAR FORMATS

SELECT TRUNC(datey, 'YYYY') FROM tr_date_tests;
+----------------------+
| TRUNC(datey, 'YYYY') |
+----------------------+
| 2019-01-01 00:00:00  |
+----------------------+
SELECT TRUNC(datey, 'SYYYY') FROM tr_date_tests;
+-----------------------+
| TRUNC(datey, 'SYYYY') |
+-----------------------+
| 2019-01-01 00:00:00   |
+-----------------------+
SELECT TRUNC(datey, 'YEAR') FROM tr_date_tests;
+----------------------+
| TRUNC(datey, 'YEAR') |
+----------------------+
| 2019-01-01 00:00:00  |
+----------------------+
SELECT TRUNC(datey, 'SYEAR') FROM tr_date_tests;
+-----------------------+
| TRUNC(datey, 'SYEAR') |
+-----------------------+
| 2019-01-01 00:00:00   |
+-----------------------+
SELECT TRUNC(datey, 'YYY') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'YYY') |
+---------------------+
| 2019-01-01 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'YY') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'YY')  |
+---------------------+
| 2019-01-01 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'Y') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'Y')   |
+---------------------+
| 2019-01-01 00:00:00 |
+---------------------+

ISO YEAR FORMAT

SELECT TRUNC(datey, 'IYYY') FROM tr_date_tests;
+----------------------+
| TRUNC(datey, 'IYYY') |
+----------------------+
| 2018-12-31 00:00:00  |
+----------------------+
SELECT TRUNC(datey, 'IY') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'IY')  |
+---------------------+
| 2018-12-31 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'I') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'I')   |
+---------------------+
| 2018-12-31 00:00:00 |
+---------------------+

QUARTER

SELECT TRUNC(datey, 'Q') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'Q')   |
+---------------------+
| 2019-07-01 00:00:00 |
+---------------------+

MONTH

SELECT TRUNC(datey, 'MONTH') FROM tr_date_tests;
+-----------------------+
| TRUNC(datey, 'MONTH') |
+-----------------------+
| 2019-09-01 00:00:00   |
+-----------------------+
SELECT TRUNC(datey, 'MON') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'MON') |
+---------------------+
| 2019-09-01 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'MM') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'MM')  |
+---------------------+
| 2019-09-01 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'RM') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'RM')  |
+---------------------+
| 2019-09-01 00:00:00 |
+---------------------+

DAY

SAME DAY

SELECT TRUNC(datey, 'DDD') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'DDD') |
+---------------------+
| 2019-09-10 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'DD') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'DD')  |
+---------------------+
| 2019-09-10 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'J') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'J')   |
+---------------------+
| 2019-09-10 00:00:00 |
+---------------------+

FIRST DAY OF WEEK

SELECT TRUNC(datey, 'DAY') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'DAY') |
+---------------------+
| 2019-09-08 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'DY') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'DY')  |
+---------------------+
| 2019-09-08 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'D') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'D')   |
+---------------------+
| 2019-09-08 00:00:00 |
+---------------------+

HOUR

SELECT * FROM tr_date_tests;
+---------------------+
| datey               |
+---------------------+
| 2019-09-10 11:17:22 |
+---------------------+
SELECT TRUNC(datey, 'HH') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'HH')  |
+---------------------+
| 2019-09-10 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'HH12') FROM tr_date_tests;
+----------------------+
| TRUNC(datey, 'HH12') |
+----------------------+
| 2019-09-10 00:00:00  |
+----------------------+

MINUTE

SELECT TRUNC(datey, 'MI') FROM tr_date_tests;
+---------------------+
| TRUNC(datey, 'MI')  |
+---------------------+
| 2019-09-10 11:17:00 |
+---------------------+

WEEK

WW returns the same day within the week as the first day of the year. In this example, the start of that week is 9/15. The first day of the year, 1/1/2019, is a Tuesday, so it returns the Tuesday within that week (2019-09-17).

SELECT TRUNC(TO_DATE('2019-09-17', 'YYYY-MM-DD'), 'WW');
+--------------------------------------------------+
| TRUNC(TO_DATE('2019-09-17', 'YYYY-MM-DD'), 'WW') |
+--------------------------------------------------+
| 2019-09-17 00:00:00                              |
+--------------------------------------------------+

Similarly, for ISO week, this example returns the same day within that week of the ISO year, which was Monday.

SELECT TRUNC(TO_DATE('2019-09-17', 'YYYY-MM-DD'), 'IW');
+--------------------------------------------------+
| TRUNC(TO_DATE('2019-09-17', 'YYYY-MM-DD'), 'IW') |
+--------------------------------------------------+
| 2019-09-16 00:00:00                              |
+--------------------------------------------------+

W returns the day within that week that aligns with the first day of the month. September 1st, 2019 is a Sunday, so this example returns 2019/9/15, the Sunday of the week of the day specified.

SELECT TRUNC(TO_DATE('2019-09-17', 'YYYY-MM-DD'), 'W');
+-------------------------------------------------+
| TRUNC(TO_DATE('2019-09-17', 'YYYY-MM-DD'), 'W') |
+-------------------------------------------------+
| 2019-09-15 00:00:00                             |
+-------------------------------------------------+

Using an Alternative Date Syntax

SELECT TRUNC(TO_DATE('17/09/19', 'DD/MM/YY'), 'W');
+---------------------------------------------+
| TRUNC(TO_DATE('17/09/19', 'DD/MM/YY'), 'W') |
+---------------------------------------------+
| 2019-09-15 00:00:00                         |
+---------------------------------------------+

Last modified: May 31, 2023

Was this article helpful?

On this page

Was this article helpful?