MySQL DATE_FORMAT() 函數 (格式化日期時間顯示)

DATE_FORMAT() 是 MySQL 中用來將日期時間值格式化為指定格式的字串的函數。它非常靈活，可以自定義各種日期時間的顯示格式。

DATE_FORMAT() 語法 (Syntax)

DATE_FORMAT(date, format)
  • date：要格式化的日期或日期時間值。
  • format：格式字串，使用特定的格式說明符。

常用格式說明符

格式說明範例值
%Y四位數年份2024
%y兩位數年份24
%m月份（兩位數，01-12）11
%c月份（數字，1-12）11
%M月份名稱（英文全名）November
%b月份名稱（英文縮寫）Nov
%d日期（兩位數，01-31）22
%e日期（數字，1-31）22
%D日期（帶英文後綴）22nd
%H小時（24 小時制，00-23）14
%h小時（12 小時制，01-12）02
%I小時（12 小時制，01-12）02
%k小時（24 小時制，0-23）14
%l小時（12 小時制，1-12）2
%i分鐘（00-59）30
%s秒（00-59）45
%S秒（00-59）45
%f微秒（000000-999999）123456
%pAM 或 PMPM
%r12 小時制時間（hh:mm:ss AM/PM）02:30:45 PM
%T24 小時制時間（HH:MM:SS）14:30:45
%W星期幾（英文全名）Friday
%a星期幾（英文縮寫）Fri
%w星期幾（數字，0=Sunday）5
%j一年中的第幾天（001-366）327
%U一年中的第幾週（00-53，週日為一週開始）47
%u一年中的第幾週（00-53，週一為一週開始）47
%VISO 週數（01-53，與 %X 配合）47
%XISO 年份（與 %V 配合）2024
%%百分號字元%

DATE_FORMAT() 函數用法範例 (Example)

基本日期格式

SELECT DATE_FORMAT('2024-11-22', '%Y-%m-%d');
-- 結果：2024-11-22

SELECT DATE_FORMAT('2024-11-22', '%Y/%m/%d');
-- 結果：2024/11/22

SELECT DATE_FORMAT('2024-11-22', '%d-%m-%Y');
-- 結果：22-11-2024

完整日期時間格式

SELECT DATE_FORMAT('2024-11-22 14:30:45', '%Y-%m-%d %H:%i:%s');
-- 結果：2024-11-22 14:30:45

SELECT DATE_FORMAT('2024-11-22 14:30:45', '%Y年%m月%d日 %H時%i分%s秒');
-- 結果：2024年11月22日 14時30分45秒

月份和星期的文字顯示

SELECT DATE_FORMAT('2024-11-22', '%W, %M %D, %Y');
-- 結果：Friday, November 22nd, 2024

SELECT DATE_FORMAT('2024-11-22', '%a, %b %d, %Y');
-- 結果：Fri, Nov 22, 2024

12 小時制時間

SELECT DATE_FORMAT('2024-11-22 14:30:45', '%h:%i:%s %p');
-- 結果：02:30:45 PM

SELECT DATE_FORMAT('2024-11-22 09:15:00', '%r');
-- 結果：09:15:00 AM

24 小時制時間

SELECT DATE_FORMAT('2024-11-22 14:30:45', '%H:%i:%s');
-- 結果：14:30:45

SELECT DATE_FORMAT('2024-11-22 14:30:45', '%T');
-- 結果：14:30:45

實際應用範例

格式化訂單日期

SELECT 
    order_id,
    DATE_FORMAT(order_date, '%Y年%m月%d日') AS formatted_date
FROM orders;
order_idformatted_date
12024年11月22日
22024年11月23日

按年月分組統計

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS year_month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY year_month;
year_monthorder_counttotal_amount
2024-1015050000
2024-1118062000

取得月份的第一天和最後一天

SELECT 
    DATE_FORMAT(NOW(), '%Y-%m-01') AS first_day_of_month,
    LAST_DAY(NOW()) AS last_day_of_month;

顯示友善的時間格式

SELECT 
    activity,
    DATE_FORMAT(created_at, '%m/%d %H:%i') AS activity_time
FROM activity_logs
ORDER BY created_at DESC
LIMIT 10;

產生檔案名稱

SELECT CONCAT('backup_', DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s'), '.sql') AS backup_filename;
-- 結果：backup_20241122_143045.sql

TIME_FORMAT() 函數

如果只需要格式化時間部分，可以使用 TIME_FORMAT() 函數：

SELECT TIME_FORMAT('14:30:45', '%H:%i');
-- 結果：14:30

SELECT TIME_FORMAT('14:30:45', '%h:%i %p');
-- 結果：02:30 PM

STR_TO_DATE() 函數（反向操作）

STR_TO_DATE() 是 DATE_FORMAT() 的反向操作，用於將字串解析為日期：

SELECT STR_TO_DATE('22-11-2024', '%d-%m-%Y');
-- 結果：2024-11-22

SELECT STR_TO_DATE('November 22, 2024', '%M %d, %Y');
-- 結果：2024-11-22

更多 MySQL 相關的日期時間函數在這邊
更多 SQL Server 相關的日期時間函數在這邊