SQL 日期與時間 (SQL Date and Time)

日期和時間是資料庫中最常處理的資料類型之一。無論是記錄訂單時間、用戶註冊日期，還是計算時間差異，都需要用到日期時間相關的操作。不同的資料庫系統提供了各自的日期時間資料型態和函數。

日期時間資料型態 (Date Data Types)

MySQL

型態	格式	範圍	說明
`DATE`	YYYY-MM-DD	1000-01-01 ~ 9999-12-31	只儲存日期
`TIME`	HH:MI:SS	-838:59:59 ~ 838:59:59	只儲存時間
`DATETIME`	YYYY-MM-DD HH:MI:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	儲存日期和時間
`TIMESTAMP`	YYYY-MM-DD HH:MI:SS	1970-01-01 00:00:01 ~ 2038-01-19 03:14:07	UTC 時間戳記，會自動轉換時區
`YEAR`	YYYY	1901 ~ 2155	只儲存年份

DATETIME vs TIMESTAMP 的差異：

DATETIME 儲存的是「絕對時間」，不會隨時區變化。
TIMESTAMP 儲存的是 UTC 時間，讀取時會自動根據資料庫連線的時區轉換。
TIMESTAMP 的範圍較小（只到 2038 年）。

SQL Server

型態	格式	範圍	說明
`DATE`	YYYY-MM-DD	0001-01-01 ~ 9999-12-31	只儲存日期
`TIME`	HH:MI:SS.nnnnnnn	00:00:00.0000000 ~ 23:59:59.9999999	只儲存時間（精度可設定）
`DATETIME`	YYYY-MM-DD HH:MI:SS.mmm	1753-01-01 ~ 9999-12-31	精度到 3.33 毫秒
`DATETIME2`	YYYY-MM-DD HH:MI:SS.nnn	0001-01-01 ~ 9999-12-31	較新型態，精度可達 100 奈秒
`SMALLDATETIME`	YYYY-MM-DD HH:MI:SS	1900-01-01 ~ 2079-06-06	精度到分鐘
`DATETIMEOFFSET`	YYYY-MM-DD HH:MI:SS ±HH:MI	0001-01-01 ~ 9999-12-31	包含時區資訊

日期查詢 (Querying Dates)

假設有一個 orders 資料表：

order_id	product_name	order_date
1	Laptop	2024-11-01 10:30:00
2	Mouse	2024-11-09 14:15:00
3	Keyboard	2024-11-15 09:00:00
4	Monitor	2024-10-28 16:45:00

查詢特定日期的訂單

SELECT * FROM orders WHERE DATE(order_date) = '2024-11-09';

或者：

SELECT * FROM orders 
WHERE order_date >= '2024-11-09' AND order_date < '2024-11-10';

查詢日期區間內的訂單

使用比較運算子：

SELECT * FROM orders 
WHERE order_date >= '2024-11-01' AND order_date < '2024-12-01';

或使用 BETWEEN 運算子：

SELECT * FROM orders 
WHERE DATE(order_date) BETWEEN '2024-11-01' AND '2024-11-30';

⚠️ 注意： 當 order_date 包含時間部分時，使用 BETWEEN '2024-11-01' AND '2024-11-30' 可能會漏掉 11 月 30 日當天的資料（因為 '2024-11-30' 會被視為 '2024-11-30 00:00:00'）。建議使用 < '2024-12-01' 來確保包含整個月份。

日期時間函數總覽

MySQL 日期函數

函數	說明
NOW()	取得現在的日期時間
CURDATE()	取得現在的日期
CURTIME()	取得現在的時間
DATE()	從日期時間中取出日期部分
EXTRACT()	取出日期時間中特定的部分（年、月、日等）
DATE_ADD()	在日期時間上增加指定的間隔
DATE_SUB()	在日期時間上減去指定的間隔
DATEDIFF()	計算兩個日期之間相差的天數
TIMESTAMPDIFF()	計算兩個日期時間之間的間隔
DATE_FORMAT()	格式化日期時間顯示

SQL Server 日期函數

函數	說明
GETDATE()	取得現在的日期時間
DATEPART()	取出日期時間中特定的部分（年、月、日等）
DATEADD()	增加或減少指定的時間間隔
DATEDIFF()	計算兩個日期之間的時間間隔
CONVERT()	格式化日期時間顯示

常見日期操作範例

取得今天、昨天、明天

MySQL：

SELECT CURDATE() AS today,
       DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS yesterday,
       DATE_ADD(CURDATE(), INTERVAL 1 DAY) AS tomorrow;

SQL Server：

SELECT CAST(GETDATE() AS DATE) AS today,
       DATEADD(day, -1, CAST(GETDATE() AS DATE)) AS yesterday,
       DATEADD(day, 1, CAST(GETDATE() AS DATE)) AS tomorrow;

取得本月第一天和最後一天

MySQL：

SELECT DATE_FORMAT(NOW(), '%Y-%m-01') AS first_day,
       LAST_DAY(NOW()) AS last_day;

SQL Server：

SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS first_day,
       EOMONTH(GETDATE()) AS last_day;

計算年齡

MySQL：

SELECT name, birthday,
       TIMESTAMPDIFF(YEAR, birthday, CURDATE()) AS age
FROM users;

SQL Server：

SELECT name, birthday,
       DATEDIFF(year, birthday, GETDATE()) - 
       CASE WHEN DATEADD(year, DATEDIFF(year, birthday, GETDATE()), birthday) > GETDATE() 
            THEN 1 ELSE 0 END AS age
FROM users;