Outdated Version
You are viewing an older version of this section. View current production version.
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 |
+---------------------------------------------+
