How to use the DATE_FORMAT function in MySQL to convert a date into a string in a specified format

How to use the DATE_FORMAT function in MySQL to convert a date into a string in a specified format

In MySQL, we often need to convert date data into a specific format to meet specific needs. In order to achieve this purpose, MySQL provides the DATE_FORMAT function, through which date data can be converted into a string in a specified format. This article will introduce how to use the DATE_FORMAT function to implement this function and give relevant code examples.

First, let us take a look at the basic syntax of the DATE_FORMAT function:

DATE_FORMAT(date, format)

Among them, date is the date data that needs to be converted, which can be a date field or a date expression; format is specified Date format, used to define the style of the converted date string.

The format string consists of a series of date format characters, each date format character begins with a percent sign (%). The following are some common date format characters and their meanings:

• %Y: Four-digit year representation (for example: 2021)
• %m: Two-digit month representation (for example : 01 month)
• %d: Two-digit date representation (for example: 01)
• %H: Hour representation in 24-hour format (for example: 09 hours)
• %i: minute representation (for example: 30 minutes)
• %s: second representation (for example: 10 seconds)
• %W: day of the week representation of the full name (for example: Monday)
• %M: Month representation of the full name (for example: January)

Several examples are given below to demonstrate how to use the DATE_FORMAT function to convert a date into a string in a specified format .

Example 1: Convert the date field to the format of year, month and day

Suppose we have a table named orders, which has a field named order_date, which stores the date information of the order . Now we want to convert this date field to the format of year, month and day, that is, YYYY-MM-DD.

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM orders;

Example 2: Convert date expression to 24-hour time format

Suppose we have a table named messages, which has a field named create_time, which stores messages creation time. Now we want to convert this date expression into 24-hour time format.

SELECT DATE_FORMAT(create_time, '%H:%i:%s') AS formatted_time
FROM messages;

Example 3: Convert the date field to a date string in Chinese format

Suppose we have a table named customers, which has a field named birthday, which stores the customer's Birthday messages. Now we want to convert this date field into a date string in Chinese format, that is, YYYY year MM month DD day.

SELECT DATE_FORMAT(birthday, '%Y年%m月%d日') AS formatted_birthday
FROM customers;

It should be noted that the DATE_FORMAT function does not directly modify the data in the database, but returns the converted string in the query result. If you need to write the converted string back to the database, you need to use the UPDATE statement to achieve it.

The above is how to use the DATE_FORMAT function in MySQL to convert a date into a string in a specified format. Through reasonable use of date format characters, various date string conversions can be achieved. I hope this article will help you understand the use of the DATE_FORMAT function.

The above is the detailed content of How to use the DATE_FORMAT function in MySQL to convert a date into a string in a specified format. For more information, please follow other related articles on the PHP Chinese website!