DATE_FORMAT() Examples – MySQL

by

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