MySQL DATE_FORMAT() function

In this context, we will learn how we can use the MySQL DATE_FORMAT() function with proper syntax and good examples.

Introduction of MySQL DATE_FORMAT() function

DATE_FORMAT() function in MySQL is used to format a specified date as a given format value, i.e., a date will be given, and this function will format that date as specified format parameters.

Syntax of the MySQL DATE_FORMAT() function

The syntax of the MySQL DATE_FORMAT() function is given as follows:

DATE_FORMAT(dt, Sformat)

Parameters or arguments used in MySQL DATE_FORMAT() function:

There are two parameters accepted by the DATE_FORMAT() function in MySQL, which are given as follows:

Dt: it is the mentioned date to be formatted.

Sformat: it is the mentioned format. This list of formats used in this function are listed below:

Format Description of MySQL DATE_FORMAT function:

%a: This abbreviation means weekday name. Its limit is from Sun to Sat.

%b: This abbreviation means month name. Its limit is from Jan to Dec.

%c: This abbreviation means numeric month name. Its limit is from 0 to 12.

%D: This abbreviation means a day of the month as a numeric value, followed by a suffix like 1st, 2nd, etc.

%e: This abbreviation means a day of the month as a numeric value. Its limit is from 0 to 31.

%f: This abbreviation means microseconds. Its limit is from 000000 to 999999.

%H: This abbreviation means hour. Its limit is from 00 to 23.

%I: This abbreviation means minutes. Its limit is from 00 to 59.

%j: This abbreviation means the day of the year. Its limit is from 001 to 366.

%M: This abbreviation means month name from January to December.

%p: This abbreviation means AM or PM.

%S: This abbreviation means seconds. Its limit is from 00 to 59.

%U: This abbreviation means week, where Sunday is the first day of the week. Its limit is from 00 to 53.

%W: This abbreviation means weekday names from Sunday to Saturday.

%Y: This abbreviation means year as a numeric value of 4 digits.

Returns :

It will return the formatted date.

Example 1:

In this example, we will get a formatted year as “2020” from the specified date “2020-11-23” in MySQL.

SELECT DATE_FORMAT("2020-11-23", "%Y");

Output:

2020
1 row in set (0.00 sec)

Example 2:

In this example, we will get a formatted month name as “December” from the specified date “2020-12-23” in MySQL.

SELECT DATE_FORMAT("2020-12-23", "%M");

Output:

December
1 row in set (0.00 sec)

Example 3:

In this example, we will get a day of the month as a numeric value as “23rd” from the specified date “2020-11-23” in MySQL.

SELECT DATE_FORMAT("2020-11-23", "%D");

Output:

23rd
1 row in set (0.00 sec)

Example-4 :

In this example, we will get the month, day, and year as “December 23, 2020” from the specified date “2020-12-23” in MySQL.

SELECT DATE_FORMAT("2020-12-23", "%M %d %Y");

Output:

December 23 2020
1 row in set (0.00 sec)

Example 5:

In this example, we will get hour and minute as “12 09” from the specified date and time “2020-11-23 12:09:23”.

SELECT DATE_FORMAT("2020-11-23 12:09:23", "%H %i");

Output:

12 09
1 row in set (0.00 sec)

Application of MySQL DATE_FORMAT() function:

This function is used to format a specified date as a given format value, i.e., a date will be given, and this function will format it.

Summary:

In the above context, we have learned how to use the DATE_FORMAT() function in MySQL to format a specified date as a given format value, i.e., a date will be given, and this function will format it.