Sign inTry Free

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.

TRUNC

On this page

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

Last modified: May 31, 2023

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK