MySQL DATE_FORMAT()

The default date format in MySQL is ‘YYYY-MM-DD.’If the date value is not provided in the proper format when inserting data into the database. An error can be generated, or a wrong value will be inserted. Sometimes it requires to display the date value in a different format, such as ‘DD-MM-YYYY’ or ‘MM-DD-YYYY’ or month name in place of numeric value after reading the data from the database table. The DATE_FORMAT() function of MySQL is used to get the formatted date output based on the programming requirement. The ways of using this function in MySQL in different ways have been shown in this tutorial.

Syntax:

The syntax of the DATE_FORMAT() function has given below.

Here, the first argument contains a date or DateTime value from the date field of the database table. The second argument contains the format specifier defined by ‘%’ to get the formatted output of the date value. Different types of format specifiers have been described in the next part of the tutorial.

Date Format Specifiers:

One or more format specifiers can be used as the format value of the DATE_FORMAT() function. The format specifiers used to format the date in MySQL are given below.

Use of DATE_FORMAT():

Different uses of the DATE_FORMAT() function in MySQL have been shown in this part of the tutorial.

Example-1: Using a single format specifier

Three different specifiers have been used in the DATE_FORMAT() function of the following SELECT query. The %D will print the day of the month with the suffix of the date. The %M will print the full month name of the date. The %Y will print the 4-digits year value of the date.

Output:

Example-2: Using multiple format specifier

In the previous example, a single format specifier has used in each DATE_FORMAT() function. The multiple format specifiers have been used in the DATE_FORMAT() function of the following SELECT query. The formatted date value will be printed after executing the following query.

Output:

Example-3: Using multiple format specifiers for the current date

The multiple format specifiers have been used in the DATE_FORMAT() function of the following SELECT query to format the current system date. The weekday name of the current date, the day of the current date with suffix, and the 4-digits years of the current date will be printed in the output.

Output:

Example-4: Format date field of the database table

You have to create a database table with the data in a MySQL database to check the use of the DATE_FORMAT() function in the table’s data.

Open the terminal and connect with the MySQL server by executing the following command.

Run the following command to create a database named test_db.

Run the following command to select the database.

Run the following query to create a table named employees with four fields.

Run the following query to insert two records into the employees table.

Run the following two SELECT queries to check the original content of the employees table and the formatted date value of the employees table.

Output:

Conclusion:

Using the DATE_FORMAT() function with different date format specifiers in the SELECT query has been described in this tutorial with the table data and without the table data. I hope the MySQL users will be able to use this function properly in the query after reading this tutorial.