Format Dates in MySQL with the DATE_FORMAT() Method

In this tutorial, you'll learn how to format dates with the DATE_FORMAT() function in MySQL. I've also provided a reference table for the different formatting options available.

The Syntax

Since MySQL 4.0, you can format dates in many different ways using the DATE_FORMAT() method. This method accepts two required parameters with the following syntax:

DATE_FORMAT(date, format)

date: Required. The date to be formatted.
format: Required. The format to use for the specified date.

Examples

You can specify date values in two different ways. The first way is by passing in a date value as plain text:

SELECT DATE_FORMAT("2022-09-22", "%M %d, %Y");
-- Output: September 22, 2022

The second way is by specifying a database column with either a date or datetime data type as the date's value in the first parameter:

SELECT DATE_FORMAT(create_date, "%M d%, %Y") FROM members;
-- Output: September 22, 2022

Both dates are formatted and output the same. These examples illustrate that you have multiple options for date selections and formatting.

Format Reference

Here is a quick reference table for date formatting options in MySQL:

Format	Description
%a	Weekday abbreviated (Sun to Sat)
%b	Month abbreviated (Jan to Dec)
%c	Month (0 to 12)
%D	Day of month with suffix (1st, 2nd, 3rd, ...)
%d	Day of month with trailing zero (01 to 31)
%e	Day of month (1 to 31)
%f	Microseconds (000000 to 999999)
%H	Hour in military time (00 to 23)
%h	Hour (00 to 12)
%I	Hour (00 to 12)
%i	Minutes (00 to 59)
%j	Day of year (001 to 366)
%k	Hour (0 to 23)
%l	Hour (1 to 12)
%M	Month name (January to December)
%m	Month number (01 to 12)
%p	AM or PM
%r	Time in 12 hour format (hh:mm:ss AM/PM)
%S	Seconds (00 to 59)
%s	Seconds (00 to 59)
%T	Time in 24 hour format (hh:mm:ss)
%U	Week with Sunday as first weekday (00 to 53)
%u	Week with Monday as first weekday (00 to 53)
%V	Week with Sunday as first weekday (01 to 53). Used with %X
%v	Week with Monday as first weekday (01 to 53). Used with %x
%W	Weekday (Sunday to Saturday)
%w	Weekday (Sunday = 0, Saturday = 6)
%X	Year for week with Sunday as first weekday. Used with %V
%x	Year for week with Monday as first weekday. Used with %v
%Y	Year as 4-digit value
%y	Year as 2-digit value

As you can probably tell, text case matters when selecting a formatting option, so double check you've entered the correct formatting option when testing your queries.

Conclusion

This article taught you the ways of date formatting in MySQL using the DATE_FORMAT() method and provided a reference table for the different formatting options.

Last Updated: September 27, 2022
Created: September 22, 2022