Skip to main content

DATE_FORMAT

Description

The DATE_FORMAT function is used to convert a date or time value into a string according to a specified format string (format). It supports formatting DATE (date only) and DATETIME (date and time) types, and the output result is a string that conforms to the specified format.

This function is consistent with the date_format function in MySQL.

Syntax

DATE_FORMAT(<date_or_time_expr>, <format>)

Parameters

ParameterDescription
<date_or_time_expr>A valid date value, supporting datetime or date type. For specific datetime and date formats, please refer to datetime conversion and date conversion
<format>Specifies the output format of the date/time, of varchar type

Supported format specifiers:

Format SpecifierDescription
%aAbbreviated weekday name (3 letters)
%bAbbreviated month name (3 letters)
%cMonth as a numeric value (0-12)
%DDay of the month with an English suffix (0th, 1st, 2nd, 3rd, …)
%dDay of the month as a numeric value (00-31)
%eDay of the month as a numeric value (0-31)
%fMicroseconds (000000-999999)
%HHour (00-23)
%hHour (01-12)
%IHour (01-12)
%iMinutes as a numeric value (00-59)
%jDay of year (001-366)
%kHour (0-23)
%lHour (1-12)
%MMonth name
%mMonth as a numeric value (00-12)
%pAM or PM
%rTime in 12-hour format (hh:mm:ss followed by AM or PM)
%SSeconds (00-59)
%sSeconds (00-59)
%TTime in 24-hour format (hh:mm:ss)
%UWeek (00-53), where Sunday is the first day of the week, week mode 0
%uWeek (00-53), where Monday is the first day of the week, week mode 1
%VWeek (01-53), where Sunday is the first day of the week, week mode 2, used with %X
%vWeek (01-53), where Monday is the first day of the week, week mode 3, used with %x
%WFull weekday name (Sunday-Saturday)
%wDay of the week (0 = Sunday, 6 = Saturday)
%XYear, where Sunday is the first day of the week (4 digits), used with %V
%xYear, where Monday is the first day of the week (4 digits), used with %v
%YYear (4 digits)
%yYear (2 digits)
%%Represents the % character
%xFor any x not listed above, represents x itself

Three special formats are also available:

yyyyMMdd --corresponds to standard format specifier:%Y%m%d
yyyy-MM-dd --corresponds to standard format specifier:%Y-%m-%d
yyyy-MM-dd HH:mm:ss --corresponds to standard format specifier:%Y-%m-%d %H:%i:%s

Return Value

A formatted date string, type is Varchar.

Special cases:

  • Returns NULL if format is NULL.
  • Returns NULL if any parameter is NULL.
  • Returns error if input value exceeds 128 characters length.
  • output length over 102 size,throw error

Examples

-- Output weekday name, month name, and 4-digit year
select date_format(cast('2009-10-04 22:23:00' as datetime), '%W %M %Y');

+------------------------------------------------------------------+
| date_format(cast('2009-10-04 22:23:00' as datetime), '%W %M %Y') |
+------------------------------------------------------------------+
| Sunday October 2009 |
+------------------------------------------------------------------+

-- Output time in 24-hour format (hour:minute:second)
select date_format('2007-10-04 22:23:00', '%H:%i:%s');

+------------------------------------------------+
| date_format('2007-10-04 22:23:00', '%H:%i:%s') |
+------------------------------------------------+
| 22:23:00 |
+------------------------------------------------+

-- Combine multiple format specifiers and plain characters
select date_format('1900-10-04 22:23:00', 'Day: %D, Year: %y, Month: %b, DayOfYear: %j');

+-----------------------------------------------------------------------------------+
| date_format('1900-10-04 22:23:00', 'Day: %D, Year: %y, Month: %b, DayOfYear: %j') |
+-----------------------------------------------------------------------------------+
| Day: 4th, Year: 00, Month: Oct, DayOfYear: 277 |
+-----------------------------------------------------------------------------------+

-- %X (year) used with %V (week number), where Sunday is the first day of the week
select date_format('1999-01-01 00:00:00', '%X-%V');

+---------------------------------------------+
| date_format('1999-01-01 00:00:00', '%X-%V') |
+---------------------------------------------+
| 1998-52 |
+---------------------------------------------+

-- Output the % character (escaped with %%)
select date_format(cast('2006-06-01' as date), '%%%d/%m');

+----------------------------------------------------+
| date_format(cast('2006-06-01' as date), '%%%d/%m') |
+----------------------------------------------------+
| %01/06 |
+----------------------------------------------------+

-- Special format yyyy-MM-dd HH:mm:ss
select date_format('2023-12-31 23:59:59', 'yyyy-MM-dd HH:mm:ss');
+-----------------------------------------------------------+
| date_format('2023-12-31 23:59:59', 'yyyy-MM-dd HH:mm:ss') |
+-----------------------------------------------------------+
| 2023-12-31 23:59:59 |
+-----------------------------------------------------------+

-- String that does not reference any time information
select date_format('2023-12-31 23:59:59', 'ghg');
+-------------------------------------------+
| date_format('2023-12-31 23:59:59', 'ghg') |
+-------------------------------------------+
| ghg |
+-------------------------------------------+

-- Special format yyyyMMdd
select date_format('2023-12-31 23:59:59', 'yyyyMMdd');
+------------------------------------------------+
| date_format('2023-12-31 23:59:59', 'yyyyMMdd') |
+------------------------------------------------+
| 20231231 |
+------------------------------------------------+

-- Special format yyyy-MM-dd
select date_format('2023-12-31 23:59:59', 'yyyy-MM-dd');
+--------------------------------------------------+
| date_format('2023-12-31 23:59:59', 'yyyy-MM-dd') |
+--------------------------------------------------+
| 2023-12-31 |
+--------------------------------------------------+

-- Parameter is null
mysql> select date_format(NULL, '%Y-%m-%d');
+-------------------------------+
| date_format(NULL, '%Y-%m-%d') |
+-------------------------------+
| NULL |
+-------------------------------+

---Returns error if the result string exceeds the function's maximum length limit.
mysql> select date_format('2022-01-12',repeat('a',129));
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[INVALID_ARGUMENT]Operation date_format of invalid or oversized format is invalid

---output length over 102 size,throw error
mysql> select date_format('2022-11-13 10:12:12',repeat('%h',52));
ERROR 1105 (HY000): errCode = 2, detailMessage = (10.16.10.3)[INVALID_ARGUMENT]Operation date_format of 142335809712816128,%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h%h is invalid