All Products
Search

This Product

    MaxCompute:TO_DATE

    Document Center

    MaxCompute:TO_DATE

    Last Updated:Oct 23, 2025

    Transforms a string that adheres to the specified format into a standard date value.

    Syntax

    DATETIME|DATE TO_DATE(STRING <date>[, STRING <format>])

    Parameters

    Parameter

    Required

    Description

    date

    Yes

    STRING type, a date value that meets the format. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted into a STRING before calculation. The function also supports date strings in the ISO 8601 format.

    format

    No

    A constant of the STRING type. This parameter specifies the date format. format does not support date extension formats. Other characters are ignored as useless characters during parsing.

    Note

    • Must contain at least yyyy and can only appear once. Otherwise, NULL is returned.

    • yyyy: four-digit year, mm: two-digit month, dd: two-digit day, hh: 24-hour format hour, mi: two-digit minute, ss: two-digit second, ff3: three-digit millisecond.

    Return value

    The function returns a DATE or DATETIME type value:

    • When the function is called without the format parameter, it returns a DATE type value in the yyyy-mm-dd format.

    • When the function is called with the format parameter, it returns a DATETIME type value in the yyyy-mm-dd hh:mi:ss format.

    • If the date or format value is NULL, the function returns NULL.

    Examples

    Static data example

    -- Returns 2025-01-29
SELECT TO_DATE('2025-01-29');

-- Returns 2025-01-27 00:00:00
SELECT TO_DATE('Alibaba2025-01*27', 'Alibabayyyy-mm*dd');

-- Returns 2025-01-12 00:00:00
SELECT TO_DATE('20250112', 'yyyymmdd');

-- Returns 2025-01-28 12:12:00
SELECT TO_DATE('202501281212', 'yyyymmddhhmi');

-- Returns NULL. '2025112' cannot be converted to a standard date value, causing an error. It should be '20250112'
SELECT TO_DATE('2025112', 'yyyymmdd');

-- Returns NULL. 'Alibaba2025-12*3' cannot be converted to a standard date value, causing an error. It should be 'Alibaba2025-12*03'.
SELECT TO_DATE('Alibaba2025-12*3', 'Alibabayyyy-mm*dd');

-- Returns NULL. '2025-24-01' cannot be converted to a standard date value, causing an error. It should be '2025-01-24'.
SELECT TO_DATE('2025-24-01', 'yyyy');

-- Returns 2025-10-30 15:13:12
SELECT TO_DATE('20251030 15-13-12.345','yyyymmdd hh-mi-ss.ff3');

-- Returns NULL.
SELECT TO_DATE(NULL, 'yyyymmdd hh-mi-ss.ff3');

-- Returns NULL.
SELECT TO_DATE('20251030 15-13-12.345', NULL);

-- Returns ISO 8601 format, 2025-09-24 13:39:34
SELECT TO_DATE('2025-09-24T13:39:34.119Z', 'yyyy-MM-ddThh:mi:ss.ff3Z');

    Table data example

    1. Example data.

      CREATE TABLE IF NOT EXISTS mf_date_fun_t(
    id      INT,
    date1   DATE,
    datetime1   DATETIME,
    timestamp1 TIMESTAMP,
    date2   DATE,
    datetime2   DATETIME,
    timestamp2 TIMESTAMP,
    date3 STRING,
    date4 BIGINT);

INSERT INTO mf_date_fun_t VALUES 
(1,DATE'2021-11-29',DATETIME'2021-11-29 00:01:00',TIMESTAMP'2021-01-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-20',123456780),
(2,DATE'2021-11-28',DATETIME'2021-11-28 00:02:00',TIMESTAMP'2021-02-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-21',123456781),
(3,DATE'2021-11-27',DATETIME'2021-11-27 00:03:00',TIMESTAMP'2021-03-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-22',123456782),
(4,DATE'2021-11-26',DATETIME'2021-11-26 00:04:00',TIMESTAMP'2021-04-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-23',123456783),
(5,DATE'2021-11-25',DATETIME'2021-11-25 00:05:00',TIMESTAMP'2021-05-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-24',123456784),
(6,DATE'2021-11-24',DATETIME'2021-11-24 00:06:00',TIMESTAMP'2021-06-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-25',123456785),
(7,DATE'2021-11-23',DATETIME'2021-11-23 00:07:00',TIMESTAMP'2021-07-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-26',123456786),
(8,DATE'2021-11-22',DATETIME'2021-11-22 00:08:00',TIMESTAMP'2021-08-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-27',123456787),
(9,DATE'2021-11-21',DATETIME'2021-11-21 00:09:00',TIMESTAMP'2021-09-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-28',123456788),
(10,DATE'2021-11-20',DATETIME'2021-11-20 00:10:00',TIMESTAMP'2021-10-11 00:00:00.123456789',DATE'2021-10-29',DATETIME'2021-10-29 00:00:00',TIMESTAMP'2021-10-11 00:00:00.123456789','2021-11-29',123456789);

    2. Convert the STRING type date3 field to a DATETIME type date3_to_date for output:

      SELECT date3, TO_DATE(date3, 'yyyy-mm-dd') AS date3_to_date FROM mf_date_fun_t;

      The returned result is as follows:

      +------------+---------------------+
| date3      | date3_to_date       |
+------------+---------------------+
| 2021-11-20 | 2021-11-20 00:00:00 |
| 2021-11-21 | 2021-11-21 00:00:00 |
| 2021-11-22 | 2021-11-22 00:00:00 |
| 2021-11-23 | 2021-11-23 00:00:00 |
| 2021-11-24 | 2021-11-24 00:00:00 |
| 2021-11-25 | 2021-11-25 00:00:00 |
| 2021-11-26 | 2021-11-26 00:00:00 |
| 2021-11-27 | 2021-11-27 00:00:00 |
| 2021-11-28 | 2021-11-28 00:00:00 |
| 2021-11-29 | 2021-11-29 00:00:00 |
+------------+---------------------+

    Related functions

    The TO_DATE function is part of the suite of date functions. For more information on date calculation and conversion functions, see Date functions.