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
Parameter | Description |
---|---|
<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 Specifier | Description |
---|---|
%a | Abbreviated weekday name (3 letters) |
%b | Abbreviated month name (3 letters) |
%c | Month as a numeric value (0-12) |
%D | Day of the month with an English suffix (0th, 1st, 2nd, 3rd, …) |
%d | Day of the month as a numeric value (00-31) |
%e | Day of the month as a numeric value (0-31) |
%f | Microseconds (000000-999999) |
%H | Hour (00-23) |
%h | Hour (01-12) |
%I | Hour (01-12) |
%i | Minutes as a numeric value (00-59) |
%j | Day of year (001-366) |
%k | Hour (0-23) |
%l | Hour (1-12) |
%M | Month name |
%m | Month as a numeric value (00-12) |
%p | AM or PM |
%r | Time in 12-hour format (hh:mm:ss followed by AM or PM) |
%S | Seconds (00-59) |
%s | Seconds (00-59) |
%T | Time in 24-hour format (hh:mm:ss) |
%U | Week (00-53), where Sunday is the first day of the week, week mode 0 |
%u | Week (00-53), where Monday is the first day of the week, week mode 1 |
%V | Week (01-53), where Sunday is the first day of the week, week mode 2, used with %X |
%v | Week (01-53), where Monday is the first day of the week, week mode 3, used with %x |
%W | Full weekday name (Sunday-Saturday) |
%w | Day of the week (0 = Sunday, 6 = Saturday) |
%X | Year, where Sunday is the first day of the week (4 digits), used with %V |
%x | Year, where Monday is the first day of the week (4 digits), used with %v |
%Y | Year (4 digits) |
%y | Year (2 digits) |
%% | Represents the % character |
%x | For 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