MySQL - DATE_FORMAT() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL DATE_FORMAT() function accepts date or date—time value and a format string (representing a desired date/time format) as parameters, formats the given date in the specified format and, returns the result.

Syntax

Following is the syntax of the above function –

DATE_FORMAT(date,format)

Example 1

Following example demonstrates the usage of the DATE_FORMAT() function. It prints the weekday (full), month (full) and the year of the given date–

mysql> SELECT DATE_FORMAT('2015-09-05', '%W %M %Y');
+---------------------------------------+
| DATE_FORMAT('2015-09-05', '%W %M %Y') |
+---------------------------------------+
|               Saturday September 2015 |
+---------------------------------------+
1 row in set (0.00 sec)

Example 2

Following query prints the weekday (short), month (short) and the day of the month of the given date –

mysql> SELECT DATE_FORMAT('2015-09-05', '%a %b %c');
+---------------------------------------+
| DATE_FORMAT('2015-09-05', '%a %b %c') |
+---------------------------------------+
|                             Sat Sep 9 |
+---------------------------------------+
1 row in set (0.00 sec)

Example 3

Following query formats the time value in the specified date —

mysql> SELECT DATE_FORMAT('2015-09-05 20:40:45', '%H Hours %i Minutes %S Seconds');
+----------------------------------------------------------------------+
| DATE_FORMAT('2015-09-05 20:40:45', '%H Hours %i Minutes %S Seconds') |
+----------------------------------------------------------------------+
|                                       20 Hours 40 Minutes 45 Seconds |
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

Example 4

Following is another example of this function –

mysql> SELECT DATE_FORMAT('2019-11-25 15:45:50','%D %y %a %d %m %b %j');
+-----------------------------------------------------------+
| DATE_FORMAT('2019-11-25 15:45:50','%D %y %a %d %m %b %j') |
+-----------------------------------------------------------+
|                                 25th 19 Mon 25 11 Nov 329 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

Example 5

Following query prints the time of day in 24 hours format.

mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00','%W %M %Y %r');
+--------------------------------------------------+
| DATE_FORMAT('1997-10-04 22:23:00','%W %M %Y %r') |
+--------------------------------------------------+
|                Saturday October 1997 10:23:00 PM |
+--------------------------------------------------+
1 row in set (0.00 sec)

Example 6

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below –

mysql> CREATE TABLE MyPlayers(
	ID INT,
	First_Name VARCHAR(255),
	Last_Name VARCHAR(255),
	Date_Of_Birth date,
	Place_Of_Birth VARCHAR(255),
	Country VARCHAR(255),
	PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

mysql> insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
mysql> insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
mysql> insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
mysql> insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
mysql> insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following query formats the value of the Date_Of_Birth column and prints those —

mysql> SELECT First_Name, Last_Name, Date_Of_Birth, Country, DATE_FORMAT(Date_Of_Birth, '%D %M %Y, %W') as FormattedDOB FROM MyPlayers;
+------------+------------+---------------+-------------+-----------------------------+
| First_Name |  Last_Name | Date_Of_Birth |     Country |                FormattedDOB |
+------------+------------+---------------+-------------+-----------------------------+
|    Shikhar |     Dhawan |    1981-12-05 |       India | 5th December 1981, Saturday |
|   Jonathan |      Trott |    1981-04-22 | SouthAfrica |  22nd April 1981, Wednesday |
|     Kumara | Sangakkara |    1977-10-27 |    Srilanka | 27th October 1977, Thursday |
|      Virat |      Kohli |    1988-11-05 |       India | 5th November 1988, Saturday |
|      Rohit |     Sharma |    1987-04-30 |       India |   30th April 1987, Thursday |
|   Ravindra |     Jadeja |    1988-12-06 |       India |  6th December 1988, Tuesday |
|      James |   Anderson |    1982-06-30 |     England |   30th June 1982, Wednesday |
+------------+------------+---------------+-------------+-----------------------------+
7 rows in set (0.00 sec)

Example 7

Suppose we have created a table named Subscribers with 5 records in it using the following queries –

mysql> CREATE TABLE Subscribers(
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionDate date
);
insert into Subscribers values('Raja', 'Premium', Date('2020-10-21'));
insert into Subscribers values('Roja', 'Basic', Date('2020-11-26'));
insert into Subscribers values('Puja', 'Moderate', Date('2021-03-07'));
insert into Subscribers values('Vanaja', 'Basic', Date('2021-02-21'));
insert into Subscribers values('Jalaja', 'Premium', Date('2021-01-30'));

In the following example we are passing the column SubscriptionDate as date value to this function –

mysql> SELECT SubscriberName, PackageName, SubscriptionDate, DATE_FORMAT(SubscriptionDate, '%D %M %y') as FormattedDate FROM Subscribers;
+----------------+-------------+------------------+------------------+
| SubscriberName | PackageName | SubscriptionDate |    FormattedDate |
+----------------+-------------+------------------+------------------+
|           Raja |     Premium |       2020-10-21 |  21st October 20 |
|           Roja |       Basic |       2020-11-26 | 26th November 20 |
|           Puja |    Moderate |       2021-03-07 |     7th March 21 |
|         Vanaja |       Basic |       2021-02-21 | 21st February 21 |
|         Jalaja |     Premium |       2021-01-30 |  30th January 21 |
+----------------+-------------+------------------+------------------+
5 rows in set (2.28 sec)

Example 8

Suppose we have created a table named SubscribersData with 5 records in it using the following queries –

mysql> CREATE TABLE SubscribersData(
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionDate date,
	SubscriptionTime time
);
insert into SubscribersData values('Raja', 'Premium', Date('2020-10-21'), Time('20:53:49'));
insert into SubscribersData values('Roja', 'Basic', Date('2020-11-26'), Time('10:13:19'));
insert into SubscribersData values('Puja', 'Moderate', Date('2021-03-07'), Time('05:43:20'));
insert into SubscribersData values('Vanaja', 'Basic', Date('2021-02-21'), Time('16:36:39'));
insert into SubscribersData values('Jalaja', 'Premium', Date('2021-01-30'), Time('12:45:45'));

Suppose we have created a table named SubscribersData with 5 records in it using the following queries –

mysql> SELECT SubscriberName, PackageName,
DATE_FORMAT(TIMESTAMP(SubscriptionDate, SubscriptionTime),
GET_FORMAT(TIMESTAMP, 'USA')) as TIMESTAMP
FROM SubscribersData;
+----------------+-------------+---------------------+
| SubscriberName | PackageName |           TIMESTAMP |
+----------------+-------------+---------------------+
|           Raja |     Premium | 2020-10-21 20.53.49 |
|           Roja |       Basic | 2020-11-26 10.13.19 |
|           Puja |    Moderate | 2021-03-07 05.43.20 |
|         Vanaja |       Basic | 2021-02-21 16.36.39 |
|         Jalaja |     Premium | 2021-01-30 12.45.45 |
+----------------+-------------+---------------------+
5 rows in set (0.00 sec)

Format String

There are certain characters with predefined meaning using which you can create a format string They are −

• %a – Weekday name (Sun..Sat)
• %b – Month name (Jan..Dec)
• %c – Month, numeric (0..12)
• %D – Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
• %d – Day of the month, numeric (00..31)
• %e – Day of the month, numeric (0..31)
• %f – Microseconds (000000..999999)
• %H – Hour (00..23)
• %h – Hour (01..12)
• %I – Hour (01..12)
• %i – Minutes, numeric (00..59)
• %j – Day of year (001..366)
• %k – Hour (0..23)
• %l – Hour (1..12)
• %M – Month name (January..December)
• %m – Month, numeric (00..12)
• %p – AM or PM
• %r – Time, 12-hour (hh:mm:ss followed by AM or PM)
• %S – Seconds (00..59)
• %s – Seconds (00..59)
• %T – Time, 24-hour (hh:mm:ss)
• %U – Week (00..53), where Sunday is the first day of the week; WEEK() mode 0
• %u – Week (00..53), where Monday is the first day of the week; WEEK() mode 1
• %V – Week (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
• %v – Week (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
• %W – Weekday name (Sunday..Saturday)
• %w – Day of the week (0=Sunday..6=Saturday)
• %X – Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
• %x – Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
• %Y – Year, numeric, four digits
• %y – Year, numeric (two digits)
• %% – A literal % character
• %x – x, for any “x” not listed above