TO_DATE - MaxCompute - Alibaba Cloud Documentation Center

Converts a date into a date value in a specified format.

Syntax

datetime to_date(string <date>, string <format>)

Parameters

date: required. A value of the STRING type. This parameter specifies the date string that you want to convert. If the input value is of the BIGINT, DOUBLE, DECIMAL, or DATETIME type, the value is implicitly converted into a value of the STRING type before calculation.
format: required. A constant of the STRING type. This parameter specifies the date format. format does not support extended date and time format (EDTF). Other characters are omitted as invalid characters during parsing.
The value of format must contain yyyy. Otherwise, null is returned. If redundant format strings exist in format, this function converts the date string that corresponds to the first format string into a date value. The rest strings are considered delimiters. For example, to_date("1234-2234", "yyyy-yyyy") returns 1234-01-01 00:00:00.

The format consists of different date components. yyyy indicates a 4-digit year. mm indicates a 2-digit month. dd indicates a 2-digit day. hh indicates an hour based on the base-24 numeral system. mi indicates a 2-digit minute. ss indicates a 2-digit second. ff3 indicates a 3-digit millisecond.

Return value

A value of the DATETIME type is returned. The return value is in the yyyy-mm-dd hh:mi:ss format. If the value of date or format is null, null is returned.

Sample data

This section provides sample source data for you to understand how to use date functions. In this topic, a table named mf_date_fun_t is created and data is inserted into the table. Sample statements:

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);

Query data from the mf_date_fun_t table. Sample statement:

select * from mf_date_fun_t;
-- The following result is returned: 
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
| id         | date1      | datetime1           | timestamp1              | date2      | datetime2           | timestamp2              | date3      | date4      |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+
| 1          | 2021-11-29 | 2021-11-29 00:01:00 | 2021-01-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-20 | 123456780  |
| 2          | 2021-11-28 | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-21 | 123456781  |
| 3          | 2021-11-27 | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-22 | 123456782  |
| 4          | 2021-11-26 | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-23 | 123456783  |
| 5          | 2021-11-25 | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-24 | 123456784  |
| 6          | 2021-11-24 | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-25 | 123456785  |
| 7          | 2021-11-23 | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-26 | 123456786  |
| 8          | 2021-11-22 | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-27 | 123456787  |
| 9          | 2021-11-21 | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-28 | 123456788  |
| 10         | 2021-11-20 | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123 | 2021-11-29 | 123456789  |
+------------+------------+---------------------+-------------------------+------------+---------------------+-------------------------+------------+------------+

Example: static data

-- The return value is 2010-12-03 00:00:00. 
select to_date('Alibaba 2010-12*03', 'Alibaba yyyy-mm*dd');
-- The return value is 2008-07-18 00:00:00. 
select to_date('20080718', 'yyyymmdd');
-- The return value is 2008-07-18 20:30:00. 
select to_date('200807182030','yyyymmddhhmi');
-- '2008718' cannot be converted into a standard date value, and an error is returned. The value must be written as '20080718'. 
select to_date('2008718', 'yyyymmdd');
--'Alibaba 2010-12*3' cannot be converted into a standard date value, and an error is returned. The value must be written as 'Alibaba 2010-12*03'. 
select to_date('Alibaba 2010-12*3', 'Alibaba yyyy-mm*dd');
--'2010-24-01' cannot be converted into a standard date value, and an error is returned. The value must be written as '2010-01-24'. 
select to_date('2010-24-01', 'yyyy');
-- The return value is 2018-10-30 15:13:12.
select to_date('20181030 15-13-12.345','yyyymmdd hh-mi-ss.ff3');
-- The value null is returned. 
select to_date(null,'yyyymmdd hh-mi-ss.ff3');
-- The value null is returned. 
select to_date('20181030 15-13-12.345',null);

Example: table data

Convert date values in the date3 column into date values in a specified format. Data in Sample data is used in this example. Sample statement:

select date3, to_date(date3, 'yyyy-mm-dd') as date3_to_date from mf_date_fun_t;

The following result is returned:

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