How to format date into a specific string using DATE_FORMAT function in MySQL

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

Date is one of the common data types in databases. It contains information such as year, month, day and so on. Sometimes we need to display the date in a specific format to meet different needs. The DATE_FORMAT function in MySQL can help us achieve this purpose. This article will introduce in detail how to use the DATE_FORMAT function to format dates.

First, let us understand the basic syntax of the DATE_FORMAT function:
DATE_FORMAT(date, format)

The date parameter specifies the date to be formatted, which can be a date field , a date variable or a date constant; the format parameter specifies the date output format.

The following are the meanings of some commonly used date format strings:

• %Y: Four-digit representation of the year
• %m: Two-digit representation of the month (01-12)
• %d: Two-digit date representation (01-31)
• %H: Two-digit representation of hour (24-hour format) (00-23)
• %i: two-digit representation of minutes (00-59)
• %s: two-digit representation of seconds (00-59)
• %W: representation of the full name of the week (For example: Monday)
• %M: The full name of the month (for example: January)
• %b: The abbreviation of the month (for example: Jan)
• %D : Dates with English suffixes (for example: 1st, 2nd, 3rd)

The following are some common examples showing how to use the DATE_FORMAT function to format dates.

1. Format date as year and month:

   SELECT DATE_FORMAT('2022-05-15', '%m-%Y') AS formatted_date;
   
   输出结果：05-2022

2. Format date as complete date:

   SELECT DATE_FORMAT('2022-05-15', '%Y-%m-%d') AS formatted_date;
   
   输出结果：2022-05-15

3. Format the date with the English suffix:

   SELECT DATE_FORMAT('2022-05-01', '%D %M, %Y') AS formatted_date;
   
   输出结果：1st May, 2022

4. Format the date with the full name of the week and the complete day:

   SELECT DATE_FORMAT('2022-05-15', '%W, %Y-%m-%d') AS formatted_date;
   
   输出结果：Sunday, 2022-05-15

5. Format date time into hours, minutes and seconds:

   SELECT DATE_FORMAT('2022-05-15 15:30:45', '%H:%i:%s') AS formatted_time;
   
   输出结果：15:30:45

It should be noted that the DATE_FORMAT function can not only be used in SELECT statements, but also INSERT and UPDATE statements to ensure that dates are stored in the database in the specified format.

When using the DATE_FORMAT function, we can also combine it with other SQL statements and functions, such as using CURDATE() to obtain the current date, using DATE_ADD() to perform date addition and subtraction operations, etc., to meet more complex dates. Formatting requirements.

To summarize, the DATE_FORMAT function is a very useful function in MySQL, which can format dates into specific strings. By flexibly using different formatting strings, we can easily implement customized formatted display of dates. If you are using date data type in the database and want to display the date in a specific format, then the DATE_FORMAT function can help you achieve this goal.

I hope this article will help you understand and use the DATE_FORMAT function in MySQL, and I wish you smooth application in development!

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