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 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 |
+---------------------------------------------+