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.
Created: September 22, 2022