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