SingleStore Managed Service

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