DATE_FORMAT() function in MySQL is used to format a specified date as given format value i.e., a date will be given and this function will format that date as specified format parameters.
Syntax :
DATE_FORMAT(date, format)
Parameter : This function accepts two parameters as given below :
- date – Specified date to be formatted.
- format – Specified format. 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. |
Returns :
It returns the formatted date.
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
Example-5 :
Getting hour and minute as “12 09” from the specified date and time “2020-11-23 12:09:23”.
SELECT DATE_FORMAT("2020-11-23 12:09:23", "%H %i");
Output :
12 09
Application : This function is used to format a specified date as given format value.