# 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 ```sql SELECT * FROM tr_date_tests; ``` ```output +------------+ | datey | +------------+ | 2019-09-10 | +------------+ ``` ## YEAR FORMATS ```sql SELECT TRUNC(datey, 'YYYY') FROM tr_date_tests; ``` ```output +----------------------+ | TRUNC(datey, 'YYYY') | +----------------------+ | 2019-01-01 00:00:00 | +----------------------+ ``` ```sql SELECT TRUNC(datey, 'SYYYY') FROM tr_date_tests; ``` ```output +-----------------------+ | TRUNC(datey, 'SYYYY') | +-----------------------+ | 2019-01-01 00:00:00 | +-----------------------+ ``` ```sql SELECT TRUNC(datey, 'YEAR') FROM tr_date_tests; ``` ```output +----------------------+ | TRUNC(datey, 'YEAR') | +----------------------+ | 2019-01-01 00:00:00 | +----------------------+ ``` ```sql SELECT TRUNC(datey, 'SYEAR') FROM tr_date_tests; ``` ```output +-----------------------+ | TRUNC(datey, 'SYEAR') | +-----------------------+ | 2019-01-01 00:00:00 | +-----------------------+ ``` ```sql SELECT TRUNC(datey, 'YYY') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'YYY') | +---------------------+ | 2019-01-01 00:00:00 | +---------------------+ ``` ```sql SELECT TRUNC(datey, 'YY') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'YY') | +---------------------+ | 2019-01-01 00:00:00 | +---------------------+ ``` ```sql SELECT TRUNC(datey, 'Y') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'Y') | +---------------------+ | 2019-01-01 00:00:00 | +---------------------+ ``` ## ISO YEAR FORMAT ```sql SELECT TRUNC(datey, 'IYYY') FROM tr_date_tests; ``` ```output +----------------------+ | TRUNC(datey, 'IYYY') | +----------------------+ | 2018-12-31 00:00:00 | +----------------------+ ``` ```sql SELECT TRUNC(datey, 'IY') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'IY') | +---------------------+ | 2018-12-31 00:00:00 | +---------------------+ ``` ```sql SELECT TRUNC(datey, 'I') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'I') | +---------------------+ | 2018-12-31 00:00:00 | +---------------------+ ``` ## QUARTER ```sql SELECT TRUNC(datey, 'Q') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'Q') | +---------------------+ | 2019-07-01 00:00:00 | +---------------------+ ``` ## MONTH ```sql SELECT TRUNC(datey, 'MONTH') FROM tr_date_tests; ``` ```output +-----------------------+ | TRUNC(datey, 'MONTH') | +-----------------------+ | 2019-09-01 00:00:00 | +-----------------------+ ``` ```sql SELECT TRUNC(datey, 'MON') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'MON') | +---------------------+ | 2019-09-01 00:00:00 | +---------------------+ ``` ```sql SELECT TRUNC(datey, 'MM') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'MM') | +---------------------+ | 2019-09-01 00:00:00 | +---------------------+ ``` ```sql SELECT TRUNC(datey, 'RM') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'RM') | +---------------------+ | 2019-09-01 00:00:00 | +---------------------+ ``` ## DAY ## SAME DAY ```sql SELECT TRUNC(datey, 'DDD') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'DDD') | +---------------------+ | 2019-09-10 00:00:00 | +---------------------+ ``` ```sql SELECT TRUNC(datey, 'DD') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'DD') | +---------------------+ | 2019-09-10 00:00:00 | +---------------------+ ``` ```sql SELECT TRUNC(datey, 'J') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'J') | +---------------------+ | 2019-09-10 00:00:00 | +---------------------+ ``` ## FIRST DAY OF WEEK ```sql SELECT TRUNC(datey, 'DAY') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'DAY') | +---------------------+ | 2019-09-08 00:00:00 | +---------------------+ ``` ```sql SELECT TRUNC(datey, 'DY') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'DY') | +---------------------+ | 2019-09-08 00:00:00 | +---------------------+ ``` ```sql SELECT TRUNC(datey, 'D') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'D') | +---------------------+ | 2019-09-08 00:00:00 | +---------------------+ ``` ## HOUR ```sql SELECT * FROM tr_date_tests; ``` ```output +---------------------+ | datey | +---------------------+ | 2019-09-10 11:17:22 | +---------------------+ ``` ```sql SELECT TRUNC(datey, 'HH') FROM tr_date_tests; ``` ```output +---------------------+ | TRUNC(datey, 'HH') | +---------------------+ | 2019-09-10 00:00:00 | +---------------------+ ``` ```sql SELECT TRUNC(datey, 'HH12') FROM tr_date_tests; ``` ```output +----------------------+ | TRUNC(datey, 'HH12') | +----------------------+ | 2019-09-10 00:00:00 | +----------------------+ ``` ## MINUTE ```sql SELECT TRUNC(datey, 'MI') FROM tr_date_tests; ``` ```output +---------------------+ | 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). ```sql SELECT TRUNC(TO_DATE('2019-09-17', 'YYYY-MM-DD'), 'WW'); ``` ```output +--------------------------------------------------+ | 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. ```sql SELECT TRUNC(TO_DATE('2019-09-17', 'YYYY-MM-DD'), 'IW'); ``` ```output +--------------------------------------------------+ | 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. ```sql SELECT TRUNC(TO_DATE('2019-09-17', 'YYYY-MM-DD'), 'W'); ``` ```output +-------------------------------------------------+ | TRUNC(TO_DATE('2019-09-17', 'YYYY-MM-DD'), 'W') | +-------------------------------------------------+ | 2019-09-15 00:00:00 | +-------------------------------------------------+ ``` ## Using an Alternative Date Syntax ```sql SELECT TRUNC(TO_DATE('17/09/19', 'DD/MM/YY'), 'W'); ``` ```output +---------------------------------------------+ | TRUNC(TO_DATE('17/09/19', 'DD/MM/YY'), 'W') | +---------------------------------------------+ | 2019-09-15 00:00:00 | +---------------------------------------------+ ``` *** Modified at: May 31, 2023 Source: [/db/v9.1/reference/sql-reference/date-and-time-functions/trunc/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/date-and-time-functions/trunc/) (An index of the documentation is available at /llms.txt)