MySQL DATE_FORMAT() Function
The DATE_FORMAT() function in MySQL allows you to customize how dates are displayed. You can specify the exact format you need, including elements like the year, month, day, and time components, using format codes.
DATE_FORMAT(): Definition and Usage
This function is essential for presenting dates in a user-friendly and consistent manner. It's widely used in reports and applications to ensure that dates are displayed according to specific requirements.
Syntax
Syntax
DATE_FORMAT(date, format)
Parameter Values
| Parameter | Description |
|---|---|
date |
The date value you want to format. This is required. |
format |
A string containing format codes that specify how the date should be displayed. This is required. See the table below for available format codes. |
Format Codes
| Format Code | Description |
|---|---|
%a |
Abbreviated weekday name (Sun, Mon, Tue, etc.) |
%b |
Abbreviated month name (Jan, Feb, Mar, etc.) |
%c |
Month as a number (01-12) |
%D |
Day of the month with suffix (1st, 2nd, 3rd, etc.) |
%d |
Day of the month as a number (01-31) |
%e |
Day of the month as a number (1-31) |
%f |
Microseconds (000000-999999) |
%H |
Hour (00-23, 24-hour format) |
%h |
Hour (01-12, 12-hour format) |
%I |
Hour (01-12, 12-hour format) |
%i |
Minutes (00-59) |
%j |
Day of year (001-366) |
%k |
Hour (0-23, 24-hour format) |
%l |
Hour (1-12, 12-hour format) |
%M |
Full month name (January, February, etc.) |
%m |
Month as a number (01-12) |
%p |
AM or PM |
%r |
Time in 12-hour format (hh:mm:ss AM/PM) |
%S |
Seconds (00-59) |
%s |
Seconds (00-59) |
%T |
Time in 24-hour format (hh:mm:ss) |
%U |
Week number (00-53), Sunday as first day |
%u |
Week number (00-53), Monday as first day |
%V |
Week number (01-53), Sunday as first day (use with %X) |
%v |
Week number (01-53), Monday as first day (use with %x) |
%W |
Full weekday name (Sunday, Monday, etc.) |
%w |
Day of week (0-6, Sunday=0) |
%X |
Year for the week (use with %V) |
%x |
Year for the week (use with %v) |
%Y |
Year as a four-digit number |
%y |
Year as a two-digit number |
Examples
Formatting a Date (Year Only)
Extracting only the year from a date.
Syntax
SELECT DATE_FORMAT("2017-06-15", "%Y");
Output
2017
Formatting a Date (Month, Day, Year)
Displaying the month, day, and year in a specific format.
Syntax
SELECT DATE_FORMAT("2017-06-15", "%M %d %Y");
Output
June 15 2017
Formatting a Date (Weekday, Month, Day, Year)
A more comprehensive date format.
Syntax
SELECT DATE_FORMAT("2017-06-15", "%W %M %e %Y");
Output
Thursday June 15 2017
Formatting Dates from a Table Column
Formatting birth dates from an 'Employees' table (assuming a 'BirthDate' column).
Syntax
SELECT DATE_FORMAT(BirthDate, "%W %M %e %Y") FROM Employees;
Output
(This will return a formatted birthdate for each employee in the Employees table)