All Products
Search

This Product

    MaxCompute:DATE_FORMAT

    Document Center

    MaxCompute:DATE_FORMAT

    Last Updated:Jan 15, 2024

    You can use the DATE_FORMAT function to convert a date value of the DATE, TIMESTAMP, or STRING data type into a string in the specified format. This topic describes the syntax of the DATE_FORMAT function and provides an example on how to use the function.

    Syntax

    string date_format(date|timestamp|string <date>, string <format>)

    Parameters

    • date: required. The date value that you want to convert. The date value can be of the DATE, TIMESTAMP, or STRING type.

      • Date values of the DATE or STRING type are supported only when the Hive-compatible data type edition is enabled. You can run the set odps.sql.hive.compatible=true; command to enable the Hive-compatible data type edition.

      • If the date value is of the STRING type, the following formats are supported:

        • 'yyyy-MM-dd', such as '2019-12-27'.

        • 'yyyy-MM-dd hh:mm:ss', such as '2019-12-27 12:23:10'.

        • 'yyyy-MM-dd hh:mm:ss.SSS', such as '2019-12-27 12:23:10.123'.

    • format: required. A constant of the STRING type. This parameter specifies the date format. Examples: yyyy-MM-dd hh:mm:ss.SSS and yyyy-MM-dd hh:mi:ss.SSS. format consists of the following components:

      • YYYY or yyyy: the year. yyyy represents the calendar year and YYYY represents the year of the week.

        Note

        The year of the week may be inconsistent with the actual year. Exercise caution when you use the year of the week format.

      • MM: the month

      • mm: the minute

      • dd: the day

      • HH: the hour that is expressed in the 24-hour clock

      • hh: the hour that is expressed in the 12-hour clock

      • mi: the minute

      • ss: the second

      • SSS: the millisecond

      Important

      • If the Hive-compatible data type edition is disabled, both HH and hh indicate that a 24-hour clock is used. In this case, the date format must be yyyy-MM-dd hh:mi:ss. If yyyy-MM-dd hh:mm:ss is used, the mm component takes the same value as the MM component.

      • If the Hive-compatible data type edition is enabled, HH indicates that a 24-hour clock is used, and hh indicates that a 12-hour clock is used. In this case, the date format must be yyyy-MM-dd hh:mm:ss. If yyyy-MM-dd hh:mi:ss is used, null is returned.

    Return value

    A value of the STRING type is returned. The return value varies based on the following rules:

    • If the value of date is not of the DATE or TIMESTAMP type, null is returned.

    • If the value of date is null, an error is returned.

    • If the value of 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.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-20 | 123456780  |
| 2    | 2021-11-28 | 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-21 | 123456781  |
| 3    | 2021-11-27 | 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-22 | 123456782  |
| 4    | 2021-11-26 | 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-23 | 123456783  |
| 5    | 2021-11-25 | 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-24 | 123456784  |
| 6    | 2021-11-24 | 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-25 | 123456785  |
| 7    | 2021-11-23 | 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-26 | 123456786  |
| 8    | 2021-11-22 | 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-27 | 123456787  |
| 9    | 2021-11-21 | 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-28 | 123456788  |
| 10   | 2021-11-20 | 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2021-10-29 | 2021-10-29 00:00:00 | 2021-10-11 00:00:00.123456789 | 2021-11-29 | 123456789  |
+------+-------+------------+------------+-------+------------+------------+-------+------------+

    Example: static data

    -- Enable the Hive-compatible data type edition. Commit the following command together with the SQL statement. 
set odps.sql.hive.compatible=true;
-- If the current time is 2023-11-20 17:52, the return value is 2023-11-20 17:52:41.544. 
select date_format(from_utc_timestamp(current_timestamp(), 'UTC'),'yyyy-MM-dd HH:mm:ss.SSS');
-- The return value is 2022-04-24. 
select date_format('2022-04-24','yyyy-MM-dd');

    Example: table data

    Convert date values in the datetime1 and timestamp1 columns into strings in a specified format. Data in Sample data is used in this example. Sample statement: 

    -- Enable the Hive-compatible data type edition and the MaxCompute V2.0 data type edition. Commit the following command together with the SQL statement. 
set odps.sql.hive.compatible=true;
set odps.sql.type.system.odps2=true;
select datetime1, timestamp1, date_format(datetime1,'yyyy/mm/dd'),
       date_format(datetime1,'yyyy/mm/dd HH:mm:ss'),
       date_format(timestamp1,'yyyy/mm/dd HH:mm:ss')
from mf_date_fun_t;

    The following result is returned: 

    +------------+------------+-----+-----+-----+
| datetime1  | timestamp1 | _c2 | _c3 | _c4 |
+------------+------------+-----+-----+-----+
| 2021-11-29 00:01:00 | 2021-01-11 00:00:00.123456789 | 2021/01/29 | 2021/01/29 00:01:00 | 2021/00/11 00:00:00 |
| 2021-11-28 00:02:00 | 2021-02-11 00:00:00.123456789 | 2021/02/28 | 2021/02/28 00:02:00 | 2021/00/11 00:00:00 |
| 2021-11-27 00:03:00 | 2021-03-11 00:00:00.123456789 | 2021/03/27 | 2021/03/27 00:03:00 | 2021/00/11 00:00:00 |
| 2021-11-26 00:04:00 | 2021-04-11 00:00:00.123456789 | 2021/04/26 | 2021/04/26 00:04:00 | 2021/00/11 00:00:00 |
| 2021-11-25 00:05:00 | 2021-05-11 00:00:00.123456789 | 2021/05/25 | 2021/05/25 00:05:00 | 2021/00/11 00:00:00 |
| 2021-11-24 00:06:00 | 2021-06-11 00:00:00.123456789 | 2021/06/24 | 2021/06/24 00:06:00 | 2021/00/11 00:00:00 |
| 2021-11-23 00:07:00 | 2021-07-11 00:00:00.123456789 | 2021/07/23 | 2021/07/23 00:07:00 | 2021/00/11 00:00:00 |
| 2021-11-22 00:08:00 | 2021-08-11 00:00:00.123456789 | 2021/08/22 | 2021/08/22 00:08:00 | 2021/00/11 00:00:00 |
| 2021-11-21 00:09:00 | 2021-09-11 00:00:00.123456789 | 2021/09/21 | 2021/09/21 00:09:00 | 2021/00/11 00:00:00 |
| 2021-11-20 00:10:00 | 2021-10-11 00:00:00.123456789 | 2021/10/20 | 2021/10/20 00:10:00 | 2021/00/11 00:00:00 |
+------------+------------+-----+-----+-----+

    Related functions

    DATE_FORMAT is a date function. For more information about functions related to date computing and conversion, see Date functions.