Skip to content

DATE_FORMAT()

Function Description

Format date values ​​according to format string. If either parameter is NULL, the function returns NULL.

DATE_FORMAT() returns a string containing the character set and collation given by character_set_connection and collat​​ion_connection so that it can return the month and workday names containing non-ASCII characters.

Function Syntax

> DATE_FORMAT(date,format)

Parameter definition

Parameters Description
date Required parameters. The date parameter is a legal date expression.
format Required parameters. Required. format The available specifiers can be used in the following table details.

Format specifier

Info

The specifiers shown in the following table can be used for format strings. % character is required before the format specifier character. The specifier also applies to the function UNIX_TIMESTAMP().

Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (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 Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal % character
%x x, for any “x” not listed above

Example

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
+-----------------------------------------------------+
| date_format(2009-10-04 22:23:00, %W %M %Y) |
+-----------------------------------------------------+
| Sunday October 2009 |
+-----------------------------------------------------+
1 row in set (0.01 sec)

mysql> 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 |
+-----------------------------------------------------+
1 row in set (0.02 sec)

mysql> SELECT Date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
+------------------------------------------------------------------------------------------------------------------------------
| date_format(1900-10-04 22:23:00, %D %y %a %d %m %b %j) |
+------------------------------------------------------------------------------------------------------------------------------
| 4th 00 Thu 04 10 Oct 277 |
+------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
+------------------------------------------------------------------------------------------------------------------------------
| date_format(1997-10-04 22:23:00, %H %k %I %r %T %S %w) |
+------------------------------------------------------------------------------------------------------------------------------
| 22 22 10 10:23:00 PM 22:23:00 00 6 |
+------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
+------------------------------------+
| date_format(1999-01-01, %X %V) |
+------------------------------------+
| 1998 52 |
+------------------------------------+
1 row in set (0.00 sec)

CREATE TABLE t2 (f1 DATETIME);
INSERT INTO t2 (f1) VALUES ('2005-01-01');
INSERT INTO t2 (f1) VALUES ('2005-02-01');

mysql> SELECT Date_format(f1, "%m") AS d1,
         Date_format(f1, "%m") AS d2
  FROM t2
  ORDER BY Date_format(f1, "%m");
  +------+------+
  | d1 | d2 |
  +------+------+
  | 01 | 01 |
  | 02 | 02 |
  +------+------+
  2 rows in set (0.00 sec)

CREATE TABLE t5 (a int, b date);
INSERT INTO t5
  VALUES (1,
             '2000-02-05'),
            (2,
             '2000-10-08'),
            (3,
             '2005-01-03'),
            (4,
             '2007-09-01'),
            (5,
             '2022-01-01');

mysql> SELECT * FROM t5
  WHERE b = Date_format('20000205', '%Y-%m-%d');
+-------+-----------------+
| a | b |
+-------+-----------------+
| 1 | 2000-02-05 |
+-------+-----------------+
1 row in set (0.01 sec)

mysql> SELECT * FROM t5WHERE b != Date_format('20000205', '%Y-%m-%d');
+-------+-----------------+
| a | b |
+-------+-----------------+
| 2 | 2000-10-08 |
| 3 | 2005-01-03 |
| 4 | 2007-09-01 |
| 5 | 2022-01-01 |
+-------+-----------------+
4 rows in set (0.01 sec)

mysql> SELECT DATE_FORMAT("2009-01-01",'%W %d %M %Y') as valid_date;
+-------------------------------+
| valid_date |
+-------------------------------+
| Thursday 01 January 2009 |
+-------------------------------+
1 row in set (0.00 sec)

limit

Currently, the date format only supports the data formats of yyyy-mm-dd and yyyymmdd.