MySQL DATE_FORMAT

MySQL DATE_FORMAT function is one of the MySQL Date Functions, which is useful to format the date and time value as per the given specifier. In this section, we show you how to use this MySQL DATE FORMAT function to format the given date expression with an example.

MySQL DATE_FORMAT Syntax

The basic syntax of the DATE_FORMAT() Function in MySQL is as shown below:

DATE_FORMAT(Date, format_specifier);

MySQL Date Format options

The following are the list of MySQL Date format specifiers that are available to work or format Date and time.

MySQL Date Format SpecifierDescription
%aAbbreviated or short form of Weekday Name. For example, Sun,…,Thu…Sat
%bAbbreviated or short form of Month Name. For example, Jan,..Mar,…Dec.
%cThis MySQL Date format specifier returns the Numeric Month number from 1 to 12
%DDay of the Month with English Suffix. For example, 0th, 1st, 2nd, etc.
%dNumeric Day of the Month 00 to 31
%eDay of the Month (0 to 31)
%fThis MySQL DATE_FORMAT returns MicrosecondS FROM 000000 TO 999999
%HThis Mysql date format specifier returns Hour (00..23)
%hHour (00..12)
%IHour 01 to 12
%iThis Mysql date_format specifier returns numeric Minutes value from 00 to 59
%jDay of year 001 to 366
%kHour 0 to 23
%lHour (1 to 12)
%MThis MySQL date format specifier returns Month names. For example, January,…, December.
%mNumeric Month number from 00 to 12
%pThis returns AM or PM
%rReturns the Day of the Year or Day number, i.e., 1 to 366
%SReturns Seconds 00:59
%sSeconds 00:59
%TThis MySQL DATE_FORMAT returns Time in 24 hours format. hh:mm:ss
%UEqual to WEEK() function with mode 0. Here, Sunday is the First day of the Week (00..53)
%uEqual to WEEK() with mode 1. Here, Monday is the First day of the Week (00..53)
%VSame as WEEK() mode 2. Here, Sunday is the First day of the Week (01..53). Used with %X
%vEqual to WEEK() mode 3. Here, Monday is the First day of the Week (01..53). Used with %x in Mysql date_format function
%WName of the Weekday or Weekday name, i.e., Sunday, Monday,…Saturday
%wWeekday Number from 0 to 6 where 0 = Sunday and 6 = Saturday
%XNumeric Four Digit Year for the Week. Here, Sunday is the first day of the Week. Used with %V
%xNumeric Four Digit Year for the Week. Here, Monday is the first day of the Week. Used with %v
%YNumeric Year value in Four Digits
%yYear in Two Digits
%%A literal percentage character
%xx for any not x listed above

MySQL DATE_FORMAT function Examples

The following are the list examples for this Date format function in MySQL. The below-shown queries help you understand all the specifiers that are available in this Mysql Date_Format function.

MySQL DATE_FORMAT Function Example 1

Here, First, %Y returns 4 digit Year value, %y returns two digit year value. Next, %p return the AM or PM based on the given MySQL time.

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%Y');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%y');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%p');
MySQL DATE_FORMAT Function 1

MySQL DATE FORMAT Function Example 2

In this MySQL DATE_FORMAT example, First, %a returns the short form of Day Name (Mon, Sat, etc.), %W returns the Day Name (Sunday, Monday, etc.). Next, %U returns the Week number where the first day is Sunday.

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%a');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%W');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%U');
MySQL DATE_FORMAT Function 2

MySQL DATE_FORMAT Function Example 3

In this Date Function example, First, %u returns week number where Monday is the first day. Next, %V is used with %X, and it returns year and Week Number where Sunday is the First day of the Week. The third statement %x %v returns year and week number where Monday is the first day.

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%u');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%X %V');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%x %v');
MySQL DATE FORMAT Function 3

Here, %T return the time in 24 format of hh:mm:ss

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%x');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%X');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%T');
 MySQL DATE_FORMAT Function 4

MySQL DATE FORMAT Function Example 4

In this Mysql date_format example, First, %b returns short form of Month Name (Jan, Feb, Dec etc), and %M return the Month Name (January,…, December). Next, %m return the month number (January = 1,…., December = 12)

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%b');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%M');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%m');
MySQL DATE_FORMAT Function 5

MySQL DATE_FORMAT Function Example 5

In this example, First, %H returns the hours in 24 hours format (0 to 23). Next, %h and %I are used in returning the Hours Value in 12 hours format (1 to 12).

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%H');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%h');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%I %p');
MySQL DATE FORMAT Function 6

Here, in this MySQL Date Format example, %k returns the hours in 24 hours format (0 to 23), %l returns Hours in 12 hours format (1 to 12).

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%k');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%l %p');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%i');
MySQL DATE FORMAT Function 7

MySQL DATE_FORMAT Function Example 6

In this MySQL DATE_FORMAT example, First, %j returns the Day number or day of the year (1 to 366). Next, %r return time in 12 hours format along with AM or PM. The third statement returns the seconds value from a given DateTime expression.

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%j');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%r');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%s, %S');
MySQL DATE FORMAT Function 8

Here, %D returns the Day number along with English suffix, %d, and %e returns the Day number. Next, we used the %f specifier inside the Mysql date_format function to returns the Microseconds.

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%D');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%d, %e');

SELECT DATE_FORMAT('2018-12-31 23:59:02.222333', '%f');
MySQL DATE_FORMAT Function 9

MySQL DATE_FORMAT Function Example 7

In this Mysql date_format example, we are going to use multiple specifiers or a combination of more than one specifier. The first statement, %W, %M %Y, returns the Weekday Name, Month Name Year. Next statement, %W, %D %M %Y returns the Weekday Name, Day Number, Month Name, and Year. The third statement %W, %D %M %Y %r returns the Weekday Name, Day Number, Month Name Year, and Time in 12 hours format along with AM or PM.

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%W, %M %Y');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%W, %D %M %Y');

SELECT DATE_FORMAT('2018-12-31 23:59:02', '%W, %D %M %Y %r');
MySQL DATE_FORMAT Function 10