MySQL DATE_FORMAT

MySQL DATE_FORMAT function is one of the Date Functions, which is useful to format the date and time value as per the given specifier. This section shows 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 MySQL DATE_FORMAT Function is as shown below:

DATE_FORMAT(Date, format_specifier);

MySQL Date Format options

The following is the list of MySQL Date format specifiers available to work or format Date and time using the Date_Format function.

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 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 date format specifier will display Hour (00..23)
%hHour (00..12)
%IHour 01 to 12
%iThis specifier prints numeric Minutes values from 00 to 59
%jDay of year 001 to 366
%kHour 0 to 23
%lHour (1 to 12)
%MThis date format specifier prints Month names. For example, January,…, December.
%mNumeric Month number from 00 to 12
%pThis prints the AM or PM
%rThe Day of the Year or Day number, i.e., 1 to 366
%SSeconds 00:59
%sSeconds 00:59
%TThis MySQL DATE_FORMAT returns Time in 24 hours format. hh:mm:ss
%UEqual to the 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 the 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 of character.
%xx for any not x listed above

MySQL DATE_FORMAT Function Examples

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

MySQL DATE FORMAT Example

Here, %Y returns 4 digit Year value, and %y prints two digit year value. Next, %p returns 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 Example 2

In this example, First, %a returns the short form of Day Name (Mon, Sat, etc.), and %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');
DATE_FORMAT Example 2

MySQL DATE FORMAT Example 3

In this Date Function example, First, %u returns the week number, where Monday is the first day. Next, %V is used with %X, and it returns the year and Week Number where Sunday is the First day of the Week. Finally, the third statement, %x %v, returns the 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 %u, %X, %V, %x, %v format 3

Here, %T returns the time in 24 formats 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 Example 4

In this example, First, %b returns the short form of Month Name (Jan, Feb, Dec, etc.), and %M returns 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 Example 5

MySQL DATE_FORMAT Function Example 5

In this example, %H returns the hours in 24 hours format (0 to 23). Next, %h and %I return 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');
h, H, I, p arguments

In this MySQL DATE_FORMAT example, %k returns the hours in 24 hours format (0 to 23), and %l returns Hours in 12 hours format (1 to 12) date.

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 k, l, p, i arguments

In this MySQL DATE_FORMAT example, First, %j format returns the Day number from date or day of the year (1 to 366). Next, %r return time in 12 hours format along with AM or PM. Finally, 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');
%j, %r, %s, %S

Here, %D returns the Day number along with the English suffix, %d, and %e returns the Day number. Next, we used the %f specifier inside this function to produce 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');
DATE FORMAT Example 9

In this MySQL date_format example, we will use multiple specifiers or a combination of more than one specifier. The first statement, %W, %M %Y, returns the Weekday Name, Month Name Year. The following 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 10