TRUNC

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 date_tests;
****
+------------+
| datey      |
+------------+
| 2019-09-10 |
+------------+

YEAR FORMATS

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

ISO YEAR FORMAT

SELECT TRUNC(datey, 'IYYY') from date_tests;
****
+----------------------+
| TRUNC(datey, 'IYYY') |
+----------------------+
| 2018-12-31 00:00:00  |
+----------------------+
SELECT TRUNC(datey, 'IY') from date_tests;
****
+---------------------+
| TRUNC(datey, 'IY')  |
+---------------------+
| 2018-12-31 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'I') from date_tests;
****
+---------------------+
| TRUNC(datey, 'I')   |
+---------------------+
| 2018-12-31 00:00:00 |
+---------------------+

QUARTER

SELECT TRUNC(datey, 'Q') from date_tests;
****
+---------------------+
| TRUNC(datey, 'Q')   |
+---------------------+
| 2019-07-01 00:00:00 |
+---------------------+

MONTH

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

DAY

SAME DAY

SELECT TRUNC(datey, 'DDD') from date_tests;
****
+---------------------+
| TRUNC(datey, 'DDD') |
+---------------------+
| 2019-09-10 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'DD') from date_tests;
****
+---------------------+
| TRUNC(datey, 'DD')  |
+---------------------+
| 2019-09-10 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'J') from date_tests;
****
+---------------------+
| TRUNC(datey, 'J')   |
+---------------------+
| 2019-09-10 00:00:00 |
+---------------------+

FIRST DAY OF WEEK

SELECT TRUNC(datey, 'DAY') from date_tests;
****
+---------------------+
| TRUNC(datey, 'DAY') |
+---------------------+
| 2019-09-08 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'DY') from date_tests;
****
+---------------------+
| TRUNC(datey, 'DY')  |
+---------------------+
| 2019-09-08 00:00:00 |
+---------------------+
SELECT TRUNC(datey, 'D') from date_tests;
****
+---------------------+
| TRUNC(datey, 'D')   |
+---------------------+
| 2019-09-08 00:00:00 |
+---------------------+

HOUR

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

MINUTE

SELECT TRUNC(datey, 'MI') from 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                         |
+---------------------------------------------+