MySQL的DATE_FORMAT()函數(shù)用於自定義日期時(shí)間顯示格式，語法為DATE_FORMAT(date, format)，支持多種格式符如%Y、%M、%d等，可實(shí)現(xiàn)日期展示、分組統(tǒng)計(jì)等功能。

Formatting dates in MySQL is done using the DATE_FORMAT() function. This function allows you to display date and time values in a custom format by applying format specifiers. The basic syntax is:

DATE_FORMAT(date, format)
Where date is the column or expression containing the date, and format is a string with specifiers that define how the result should appear.

Common Date Format Specifiers

Here are frequently used format codes:

• %Y – 4-digit year (eg, 2024)
• %y – 2-digit year (eg, 24)
• %M – Full month name (eg, January)
• %b – Abbreviated month name (eg, Jan)
• %m – Month as numeric value (01–12)
• %d – Day of the month (01–31)
• %e – Day of the month without leading zero (1–31)
• %H – Hour (00–23)
• %h – Hour (01–12)
• %i – Minutes (00–59)
• %s – Seconds (00–59)
• %p – AM or PM

Examples of Date Formatting

Suppose you have a table named orders with a column order_date of type DATETIME. Here are some formatting examples:

• Show date as January 01, 2024 :
  DATE_FORMAT(order_date, '%M %d, %Y')
• Show date in MM/DD/YYYY format:
  DATE_FORMAT(order_date, '%m/%d/%Y')
• Show time in 12-hour format with AM/PM:
  DATE_FORMAT(order_date, '%h:%i %p')
• Full custom format like Mon, Jan 1, 2024 2:30 PM :
  DATE_FORMAT(order_date, '%a, %b %e, %Y %l:%i %p')

Using DATE_FORMAT in Queries

You can use DATE_FORMAT() directly in your SELECT statements:

This returns the date in a readable form, such as “Monday, January 1, 2024”.

It's also helpful when grouping records by year, month, or day:

Basically, DATE_FORMAT gives you full control over how dates appear in query results. Just remember to use the correct format specifiers and test your output.

以上是如何在MySQL中格式化日期？的詳細(xì)內(nèi)容。更多資訊請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！