SQL Tutorial SQL Advanced SQL Database SQL References
SQL Tutorial SQL Advanced SQL Database SQL References

MySQL DATE_FORMAT() Function

The MySQL DATE_FORMAT() function formats a date or datetime value as specified by a format mask.

Syntax

DATE_FORMAT(datetime, format_mask)

Parameters

datetime Required. Specify the date or datetime value to format.
format_mask Required. Specify the format to apply to the datetime. The following is a list of options for this parameter. It can be used in many combinations.

ValueDescription
%aWeekday name abbreviated (Sun to Sat)
%bMonth name abbreviated (Jan to Dec)
%cMonth as a numeric value (0 to 12)
%DDay of the month as a numeric value, followed by suffix (1st, 2nd, 3rd, ...)
%dDay of the month as a numeric value (01 to 31)
%eDay of the month as a numeric value (0 to 31)
%fMicroseconds (000000 to 999999)
%HHour (00 to 23)
%hHour (00 to 12)
%IHour (00 to 12)
%iMinutes (00 to 59)
%jDay of the year (001 to 366)
%kHour (00 to 23)
%lHour (1 to 12)
%MMonth name in full (January to December)
%mMonth name as a numeric value (00 to 12)
%pAM or PM
%rTime in 12 hour AM or PM format (hh:mm:ss AM/PM)
%SSeconds (00 to 59)
%sSeconds (00 to 59)
%TTime in 24 hour format (hh:mm:ss)
%UWeek where Sunday is the first day of the week (00 to 53)
%uWeek where Monday is the first day of the week (00 to 53)
%VWeek where Sunday is the first day of the week (01 to 53). Used with %X
%vWeek where Monday is the first day of the week (01 to 53). Used with %X
%WWeekday name in full (Sunday to Saturday)
%wDay of the week where Sunday=0 and Saturday=6
%XYear for the week where Sunday is the first day of the week. Used with %V
%xYear for the week where Monday is the first day of the week. Used with %v
%YYear as a numeric, 4-digit value
%yYear as a numeric, 2-digit value
%%A literal % character

Note: In MySQL, day and month range starts from 00. It allows dates to be stored incomplete. For example: '2019-00-00'.

Return Value

Returns the formatted date or datetime value as specified by a format mask.

Example 1:

The example below shows the usage of DATE_FORMAT() function.

mysql> SELECT DATE_FORMAT('2018-08-18', '%Y');
Result: '2018'

mysql> SELECT DATE_FORMAT('2018-08-18', '%M %d, %Y');
Result: 'August 18, 2018'

mysql> SELECT DATE_FORMAT('2018-08-18', '%M %e %Y');
Result: 'August 18 2018'

mysql> SELECT DATE_FORMAT('2018-08-18', '%W, %M %e, %Y');
Result: 'Saturday, August 18, 2018'

mysql> SELECT DATE_FORMAT('2018-08-18', '%W');
Result: 'Saturday'

Example 2:

Consider a database table called Employee with the following records:

EmpIDNameCityAgeDate_of_Joining
1JohnLondon252018-05-25
2MarryNew York242018-10-15
3JoParis272019-06-09
4KimAmsterdam302019-09-21
5RameshNew Delhi282019-10-25

In the query below, the DATE_FORMAT() function is used to format the date value of Date_of_Joining column:

SELECT *, 
DATE_FORMAT(Date_of_Joining, '%M %d, %Y') AS DATE_FORMAT_Value
FROM Employee;

This will produce a result similar to:

EmpIDNameCityAgeDate_of_JoiningDATE_FORMAT_Value
1JohnLondon252018-05-25May 25, 2018
2MarryNew York242018-10-15October 15, 2018
3JoParis272019-06-09June 9, 2019
4KimAmsterdam302019-09-21September 21, 2019
5RameshNew Delhi282019-10-25October 25, 2019

❮ MySQL Functions
AlphaCodingSkills
AlphaCodingSkills Android App
Facebook Page Twitter Page LinkedIn Page
Learning Tutorials
Learning Tutorials
Useful Links
Python Libraries
About Us  |  Privacy Policy  |  Contact Us
AlphaCodingSkills is a online learning portal that provides tutorials on Python, Java, C++, C, C#, PHP, R, Ruby, Rust, Scala, Swift, Perl, SQL, Data Structures and Algorithms. Tutorials, examples, references and content of the website are reviewed and simplified continuously to improve comprehensibility and eliminate any possible error. We use cookies to ensure best browsing experience on our website. While using this website, you acknowledge to have read and accepted our cookie and privacy policy.