MySQL DATE_FORMAT() Function
MySQL DATE_FORMAT() function formats a specified date to a given format value.
DATE_FORMAT() Function in MySQL
The DATE_FORMAT() function in MySQL formats a specified date or datetime according to the given format.
The DATE_FORMAT() function allows you to customize the display of date and time values in MySQL by using a combination of format specifiers.
t is commonly used in SELECT statements to format date columns for better readability and presentation.
Syntax
MySQL DATE_FORMAT function Syntax is:
DATE_FORMAT(date, format)
Parameters:
This function accepts two parameters as given below :
- date – Specified date to be formatted.
- format – Specified format.
MySQL DATE_FORMAT() Function Format Specifiers
This list of formats used in this function are listed below
| Format | Description |
|---|---|
| %a | This abbreviation means weekday name. It’s limit is from Sun to Sat. |
| %b | This abbreviation means month name. It’s limit is from Jan to Dec. |
| %c | This abbreviation means numeric month name. It’s limit is from 0 to 12. |
| %D | This abbreviation means day of the month as a numeric value, followed by suffix like 1st, 2nd, etc. |
| %e | This abbreviation means day of the month as a numeric value. It’s limit is from 0 to 31. |
| %f | This abbreviation means microseconds. It’s limit is from 000000 to 999999. |
| %H | This abbreviation means hour. It’s limit is from 00 to 23. |
| %i | This abbreviation means minutes. It’s limit is from 00 to 59. |
| %j | This abbreviation means day of the year. It’s limit is from 001 to 366. |
| %M | This abbreviation means month name from January to December. |
| %p | This abbreviation means AM or PM. |
| %S | This abbreviation means seconds. It’s limit is from 00 to 59. |
| %U | This abbreviation means week where Sunday is the first day of the week. It’s limit is from 00 to 53. |
| %W | This abbreviation means weekday name from Sunday to Saturday. |
| %Y | This abbreviation means year as a numeric value of 4-digits. |
MySQL DATE_FORMAT() Function
Let’s look at some examples of the DATE_FORMAT() function in MySQL.
Example 1
Getting a formatted year as “2020” from the specified date “2020-11-23”.
SELECT DATE_FORMAT("2020-11-23", "%Y");
Output :
2020
Example 2
Getting a formatted month name as “November” from the specified date “2020-11-23”.
SELECT DATE_FORMAT("2020-11-23", "%M");
Output :
November
Example 3
Getting a day of the month as a numeric value as “23rd” from the specified date “2020-11-23”.
SELECT DATE_FORMAT("2020-11-23", "%D");
Output :
23rd
Example-4 :
Getting month day and year as “November 23 2020” from the specified date “2020-11-23”.
SELECT DATE_FORMAT("2020-11-23", "%M %d %Y");
Output :
November 23 2020
Important Points About MySQL DATE_FORMAT() Function
- The MySQL DATE_FORMAT() function is used to format a date value based on a specific format.
- The DATE_FORMAT() function returns a string whose character set and collation depend on the settings of the client’s connection2.
- It will return NULL if any of the arguments are NULL.
- The DATE_FORMAT() function is supported in MySQL from version 4.01.
Contact Us