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:

FormatDescription
%aWeekday abbreviated (Sun to Sat)
%bMonth abbreviated (Jan to Dec)
%cMonth (0 to 12)
%DDay of month with suffix (1st, 2nd, 3rd, ...)
%dDay of month with trailing zero (01 to 31)
%eDay of month (1 to 31)
%fMicroseconds (000000 to 999999)
%HHour in military time (00 to 23)
%hHour (00 to 12)
%IHour (00 to 12)
%iMinutes (00 to 59)
%jDay of year (001 to 366)
%kHour (0 to 23)
%lHour (1 to 12)
%MMonth name (January to December)
%mMonth number (01 to 12)
%pAM or PM
%rTime in 12 hour format (hh:mm:ss AM/PM)
%SSeconds (00 to 59)
%sSeconds (00 to 59)
%TTime in 24 hour format (hh:mm:ss)
%UWeek with Sunday as first weekday (00 to 53)
%uWeek with Monday as first weekday (00 to 53)
%VWeek with Sunday as first weekday (01 to 53). Used with %X
%vWeek with Monday as first weekday (01 to 53). Used with %x
%WWeekday (Sunday to Saturday)
%wWeekday (Sunday = 0, Saturday = 6)
%XYear for week with Sunday as first weekday. Used with %V
%xYear for week with Monday as first weekday. Used with %v
%YYear as 4-digit value
%yYear 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