Use DATE_FORMAT to convert date values to a specified string format - MaxCompute

Syntax

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

Quick example:

-- Enable the Hive-compatible data type edition first.
set odps.sql.hive.compatible=true;
-- Returns: 2022-04-24
select date_format('2022-04-24', 'yyyy-MM-dd');

Parameters

date

Required. The date value to convert. Supported types: DATE, TIMESTAMP, and STRING.

DATE and STRING types are supported only when the Hive-compatible data type edition is enabled. Run set odps.sql.hive.compatible=true; to enable it.
When the input is a STRING value, the following formats are accepted:
- 'yyyy-MM-dd' — for example, '2019-12-27'
- 'yyyy-MM-dd hh:mm:ss' — for example, '2019-12-27 12:23:10'
- 'yyyy-MM-dd hh:mm:ss.SSS' — for example, '2019-12-27 12:23:10.123'

format

Required. A STRING constant that specifies the output format. Examples: yyyy-MM-dd hh:mm:ss.SSS and yyyy-MM-dd hh:mi:ss.SSS.

The format string is built from the following tokens:

Token	Meaning	Example output
`yyyy`	Calendar year	`2024`
`YYYY`	Week-based year	`2024`
`MM`	Month	`01`, `12`
`dd`	Day of month	`01`, `31`
`HH`	Hour (24-hour clock)	`00`, `23`
`hh`	Hour (12-hour clock)	`01`, `12`
`mm`	Minute	`00`, `59`
`mi`	Minute	`00`, `59`
`ss`	Second	`00`, `59`
`SSS`	Millisecond	`000`, `999`

Important

The behavior of hh and mm depends on whether the Hive-compatible data type edition is enabled:

Token	Hive-compatible disabled	Hive-compatible enabled
`hh`	24-hour clock (same as `HH`)	12-hour clock
`mm`	Same value as `MM` (month)	Minute

When the Hive-compatible data type edition is disabled, use mi for minutes and avoid mm. The correct format is yyyy-MM-dd hh:mi:ss.

When the Hive-compatible data type edition is enabled, use mm for minutes. The correct format is yyyy-MM-dd hh:mm:ss. Using mi in this mode returns null.

YYYY represents the week-based year, which may differ from the actual calendar year near year boundaries. Use yyyy (calendar year) unless you specifically need ISO week-year semantics.

Return value

Returns a STRING value. The following rules apply:

Returns null if date is not a DATE or TIMESTAMP value.
Returns an error if date is null.
Returns null if format is null.

Sample data

The examples in this topic use a table named mf_date_fun_t. Run the following statements to create and populate the table:

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

To verify the data, run:

select * from mf_date_fun_t;

Expected output:

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

Examples

Format the current timestamp

The following example formats the current UTC time as a string. Enable the Hive-compatible data type edition before running.

-- Commit the following SET command together with the SELECT 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');

Format values from a table

The following example converts the datetime1 and timestamp1 columns to strings in several formats. All examples use mm as the minute token, which requires both the Hive-compatible data type edition and the MaxCompute V2.0 data type edition.

-- Commit the following SET commands together with the SELECT 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;

Return value:

+---------------------+-------------------------------+------------+---------------------+---------------------+
| 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 date arithmetic and conversion functions, see Date functions.