DATE_FORMAT() Function in MySQL
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.
Last Updated :
03 Dec, 2020
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...