Menu

MySQL DATE_FORMAT(): Format Dates and Times with Examples

Updated on

MySQL DATE_FORMAT(date, format) returns a date or datetime as a string. Put a % before each format code: %Y-%m-%d produces an ISO-style date, while %Y-%m-%d %H:%i:%s includes 24-hour time. The function formats a value for display; it does not change the stored date or make string sorting equivalent to date sorting.

DATE_FORMAT() Syntax

Here is the syntax of MySQL DATE_FORMAT() function:

DATE_FORMAT(date, format)

Parameters

date
Required. A date or datetime value.
format
Required. The pattern to use to format the date.

The specifiers shown in the following table may be used in the format string.

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
% The following character if it is not a recognized format code

Return value

The DATE_FORMAT() function formats a date or datetime value into a specific pattern and returns the formatted date or datetime as a string.

The DATE_FORMAT() function will return NULL if any of the arguments are NULL.

DATE_FORMAT() Examples

Here are common MySQL date and time format patterns:

SELECT
    DATE_FORMAT('2026-09-30 13:05:09', '%Y-%m-%d') AS iso_date,
    DATE_FORMAT('2026-09-30 13:05:09', '%Y-%m-%d %H:%i:%s') AS iso_datetime,
    DATE_FORMAT('2026-09-30 13:05:09', '%M %e, %Y') AS long_date,
    DATE_FORMAT('2026-09-30 13:05:09', '%h:%i %p') AS time_12_hour;

+------------+---------------------+------------------+--------------+
| iso_date   | iso_datetime        | long_date        | time_12_hour |
+------------+---------------------+------------------+--------------+
| 2026-09-30 | 2026-09-30 13:05:09 | September 30, 2026 | 01:05 PM   |
+------------+---------------------+------------------+--------------+

%m is the numeric month; %i is the minute. Month and weekday names follow the session’s lc_time_names setting. The week-year specifiers %X and %x depend on the week-numbering mode; use %X %V for Sunday-first weeks or %x %v for Monday-first weeks.

Here are additional examples of the DATE_FORMAT() function.

Example 1: Formatting dates

SELECT
    DATE_FORMAT('2022-02-28', '%Y'),
    DATE_FORMAT('2014-02-28', '%W'),
    DATE_FORMAT('2022-02-01', '%M %d, %Y'),
    DATE_FORMAT('2022-02-01', '%M %e %Y'),
    DATE_FORMAT('2022-02-28', '%W, %M %e, %Y')\G

           DATE_FORMAT('2022-02-28', '%Y'): 2022
           DATE_FORMAT('2014-02-28', '%W'): Friday
    DATE_FORMAT('2022-02-01', '%M %d, %Y'): February 01, 2022
     DATE_FORMAT('2022-02-01', '%M %e %Y'): February 1 2022
DATE_FORMAT('2022-02-28', '%W, %M %e, %Y'): Monday, February 28, 2022

Example 2: Formatting datetime

SELECT NOW(), DATE_FORMAT(NOW(), '%Y%m%d%H%i%S')\G

                             NOW(): 2022-04-12 03:18:38
DATE_FORMAT(NOW(), '%Y%m%d%H%i%S'): 20220412031838

For the complete specification table and locale details, see the MySQL 8.4 DATE_FORMAT() reference.

To compare date and time formatting syntax across MySQL, MariaDB, PostgreSQL, SQL Server, SQLite, and Oracle, see date and time formatting by database.

Advertisement