MySQL: DATE_FORMAT Function With Example

MySQL: DATE_FORMAT Function With Example

MySQL: DATE_FORMAT function; In this tutorial, we will discuss how to use MySQL DATE_FORMAT function with the help of examples.

The MySQL DATE_FORMAT() function allows you to return a date in a specified format. For example, you can use it to return 2019-05-15 as May 15 2019 , or whatever other format you require.

MySQL DATE_FORMAT() function

The DATE_FORMAT() function formats a date as specified.

Syntax:

DATE_FORMAT(date,format);

Parameters or Arguments

NameDescription
date Where date is the date you want to format.
format format specifies how it should be formatted.

For a list of valid format specifiers, see the table below.

FormatDescription
%aAbbreviated weekday name (Sun to Sat)
%bAbbreviated month name (Jan to Dec)
%cNumeric month name (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 (0 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
Here are several examples to display MySQL FORMAT_DATE ().

Example-1

SELECT DATE_FORMAT('2015-07-20', '%W, %M %Y') AS 'Result';

Output-1

 +---------------------+
 | Result              |
 +---------------------+
 |Monday, July 2015    |
 +---------------------+

Example-2

Here’s an example that formats the result into short day and month names.

SELECT DATE_FORMAT('2019-05-16', '%a, %b %Y') AS 'Result';

Output-2

 +----------------+
 | Result         |
 +----------------+
 |Thu, May 2019   |
 +----------------+

Must Know Date/Time Functions

List Of MySQL Date/Time Functions

Example-3 Of Database

Here, we will take example of database table users, where we format the value returned from a database column that’s stored as a datetime value.

SELECT
  created_at AS 'Created_at',
  DATE_FORMAT( created_at , '%W, %M %Y') AS 'Date'
FROM customers
WHERE id= 1;

Output-3

 +---------------------+------------------------------+
 | Created_at          | Date                         |
 +---------------------+------------------------------+
 | 2005-05-25 11:30:37 | Wednesday, September 2010    |
 +---------------------+------------------------------+

Example-4 => Formatting the Time

You could use DATE_FORMAT() to format the time component of a datetime value.

SELECT
  created_at AS 'Created_at',
  DATE_FORMAT(created_at, '%h:%i:%s') AS 'Time'
FROM customers
WHERE id= 1;

Output-4

+---------------------+----------+
| Created_at          | Time     |
+---------------------+----------+
| 2015-08-24 10:35:47 | 12:30:38 |
+---------------------+----------+

Let’s take a new example for add the AM/PM with time.

SELECT
  created_at AS 'Created_at',
  DATE_FORMAT(created_at, '%h:%i %p') AS 'Time'
FROM customers
WHERE id= 1;

Output

+---------------------+----------+
|  Created_at         | Time     |
+---------------------+----------+
| 2015-08-24 10:35:47 | 12:30 AM |
+---------------------+----------+
More Examples here

 Mysql> SELECT DATE_FORMAT('2019-12-31 23:59:02', '%a');
 Output>
 Tue

 Mysql> SELECT DATE_FORMAT('2019-12-31 23:59:02', '%W');
 Output>
 Tuesday

 Mysql> SELECT DATE_FORMAT('2019-12-31 23:59:02', '%U');
 Output>
 52

 Mysql> SELECT DATE_FORMAT('2019-12-31 23:59:02', '%u');
 Output>
 53

 Mysql> SELECT DATE_FORMAT('2019-12-31 23:59:02', '%X %V');
 Output>
 2019 52

Conclusion

In this MySQL tutorial, we have discussed mysql DATE_FORMAT() function with various examples.

Recommended MySQL Tutorials

If you have any questions or thoughts to share, use the comment form below to reach us.

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *