TRUNC
Warning
SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.
On this page
Truncates a date using the specified format.
Returns a truncated date.
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.
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.
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 |
+---------------------------------------------+
Last modified: May 31, 2023