This article contains examples of the DATE_FORMAT() function in MySQL.

The DATE_FORMAT() function allows you to return a date in a specified format. For example, you can use it to return  2020-06-18 as Thursday, June 2020, or whatever other format you require.

Syntax

The syntax goes like this:

DATE_FORMAT(date,format)

Where date is the date you want to format, and format specifies how it should be formatted.

For a list of valid format specifiers, see the table at the bottom of this article. The format value must start with a percentage sign (%).

Example

Here’s an example to demonstrate.

SELECT DATE_FORMAT('2020-06-18', '%W, %M %Y') AS 'Result';

Result:

+---------------------+
| Result              |
+---------------------+
| Thursday, June 2020 |
+---------------------+

Short/Abbreviated Day and Month Names

Here’s an example that formats the result into short day and month names.

SELECT DATE_FORMAT('2020-06-18', '%a, %b %Y') AS 'Result';

Result:

+---------------+
| Result        |
+---------------+
| Thu, Jun 2020 |
+---------------+

A Database Example

Here’s an example where we format the value returned from a database column that’s stored as a datetime value.

USE sakila;
SELECT
  payment_date AS 'Date/Time',
  DATE_FORMAT(payment_date, '%W, %M %Y') AS 'Date'
FROM payment
WHERE payment_id = 1;

Result:

+---------------------+---------------------+
| Date/Time           | Date                |
+---------------------+---------------------+
| 2005-05-25 11:30:37 | Wednesday, May 2005 |
+---------------------+---------------------+

Formatting the Time

You can also use DATE_FORMAT() to format the time component of a datetime value. Example:

USE sakila;
SELECT
  payment_date AS 'Date/Time',
  DATE_FORMAT(payment_date, '%h:%i:%s') AS 'Time'
FROM payment
WHERE payment_id = 1;

Result:

+---------------------+----------+
| Date/Time           | Time     |
+---------------------+----------+
| 2005-05-25 11:30:37 | 11:30:37 |
+---------------------+----------+

Here’s another example where I add the AM/PM designator:

USE sakila;
SELECT
  payment_date AS 'Date/Time',
  DATE_FORMAT(payment_date, '%h:%i %p') AS 'Time'
FROM payment
WHERE payment_id = 1;

Result:

+---------------------+----------+
| Date/Time           | Time     |
+---------------------+----------+
| 2005-05-25 11:30:37 | 11:30 AM |
+---------------------+----------+

Available Specifiers

The following specifiers can be used to specify the return format. The format value must start with a percentage sign (%).

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