Open In App

DATE_FORMAT() Function in MySQL

Improve
Improve
Like Article
Like
Save
Share
Report

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
Previous
Next
Share your thoughts in the comments
Similar Reads