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:

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.