The DATE_FORMAT() function in MySQL is used to format a date or datetime value according to a specified format. This allows you to present the date in a customized way, for example, to show it in a more readable or specific format based on your application's needs.
Definition and Usage
- The
DATE_FORMAT()function formats a date or datetime expression according to the provided format string.
Syntax
DATE_FORMAT(date, format)
- date: The date or datetime to be formatted.
- format: The format string that specifies how the date should be formatted. This can include various placeholders to represent parts of the date (like day, month, year, hour, etc.).
Format Specifiers
The format string can contain one or more of the following placeholders:
| Format | Description |
|---|---|
%a |
Abbreviated weekday name (Sun to Sat) |
%b |
Abbreviated month name (Jan to Dec) |
%c |
Numeric month name (0 to 12) |
%D |
Day of the month with suffix (1st, 2nd, etc.) |
%d |
Day of the month as a numeric value (01 to 31) |
%e |
Day of the month as a numeric value (0 to 31) |
%f |
Microseconds (000000 to 999999) |
%H |
Hour (00 to 23) |
%h |
Hour (00 to 12) |
%I |
Hour (00 to 12) |
%i |
Minutes (00 to 59) |
%j |
Day of the year (001 to 366) |
%k |
Hour (0 to 23) |
%l |
Hour (1 to 12) |
%M |
Full month name (January to December) |
%m |
Month as a numeric value (00 to 12) |
%p |
AM or PM |
%r |
Time in 12-hour AM/PM 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 where Sunday is the first day of the week (00 to 53) |
%u |
Week where Monday is the first day of the week (00 to 53) |
%V |
Week where Sunday is the first day of the week (01 to 53) |
%v |
Week where Monday is the first day of the week (01 to 53) |
%W |
Full weekday name (Sunday to Saturday) |
%w |
Day of the week (Sunday = 0, Saturday = 6) |
%X |
Year for the week where Sunday is the first day of the week (used with %V) |
%x |
Year for the week where Monday is the first day of the week (used with %v) |
%Y |
Year as a 4-digit value |
%y |
Year as a 2-digit value |
Examples
Example 1: Format a date as "Month Day, Year"
SELECT DATE_FORMAT("2017-06-15", "%M %d, %Y");
Output:
June 15, 2017
This query formats the date 2017-06-15 as "June 15, 2017".
Example 2: Format a date with full weekday and month names
SELECT DATE_FORMAT("2017-06-15", "%W %M %d, %Y");
Output:
Thursday June 15, 2017
This query formats the date to show the full weekday name and month name.
Example 3: Format a date with day of the year and time in 24-hour format
SELECT DATE_FORMAT("2017-06-15 09:34:21", "%j %T");
Output:
166 09:34:21
This query formats the date to show the day of the year (166) and the time in 24-hour format.
Example 4: Format a date to show the weekday and year
SELECT DATE_FORMAT("2017-06-15", "%W %Y");
Output:
Thursday 2017
This query formats the date to show the full weekday name and the year.
Example 5: Format a birthdate in "Day of the Week, Month Day, Year" format
SELECT DATE_FORMAT(BirthDate, "%W, %M %d, %Y") FROM Employees;
This query would format each employee's BirthDate in a more readable format like "Monday, July 23, 1985".
Use Cases
- Custom Date Representation: Useful in applications that require custom date formats for reporting, user interfaces, or logging.
- Localization: Formatting dates to match regional conventions (e.g., displaying month names in the local language).
- Data Export: When exporting data, especially in CSV or other formats, you may need dates formatted in a specific way.
Related Functions
DATE(): Extracts the date part from a datetime expression.NOW(): Returns the current date and time.CURDATE(): Returns the current date.DATE_ADD(): Adds an interval to a date.