SQL Server 日期查詢陷阱與 CONVERT 格式化完整解析

問題背景

前端傳入或日曆控件產生的日期格式通常為 yyyy-mm-dd，但資料庫欄位為 datetime（含時分秒 yyyy-mm-dd hh:mm:ss）。直接用 = 或 BETWEEN 查詢會導致資料遺漏。

失效範例

-- 假設資料庫有 2008-02-27 14:30:00 的資料
WHERE fielddate = '2008-02-27'                    -- 無結果（比對 00:00:00）
WHERE fielddate BETWEEN '2008-02-27' AND '2008-02-27'  -- 同理無結果

解決方案：CONVERT 截取日期部分比對

-- 將 datetime 轉為 yyyy-mm-dd (10 字元) 再比對
WHERE CONVERT(varchar(10), fielddate, 120) = '2008-02-27'

-- 或 BETWEEN 範圍查詢
WHERE CONVERT(varchar(10), fielddate, 120) BETWEEN '2008-02-27' AND '2008-02-28'

關鍵：style 120 (ODBC 標準) 輸出 yyyy-mm-dd hh:mi:ss，varchar(10) 截取前 10 字元即日期部分。

CONVERT 格式代碼完整對照表

常用格式化範例

-- 完整日期時間
SELECT CONVERT(varchar, GETDATE(), 120)        -- 2004-09-12 11:06:08

-- 純數字時間戳 (檔案命名等)
SELECT REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120), '-', ''), ' ', ''), ':', '')  -- 20040912110608

-- 日期格式
SELECT CONVERT(varchar(10), GETDATE(), 111)    -- 2004/09/12
SELECT CONVERT(varchar(8), GETDATE(), 112)     -- 20040912
SELECT CONVERT(varchar(10), GETDATE(), 102)    -- 2004.09.12
SELECT CONVERT(varchar(10), GETDATE(), 101)    -- 09/12/2004
SELECT CONVERT(varchar(10), GETDATE(), 103)    -- 12/09/2004
SELECT CONVERT(varchar(10), GETDATE(), 104)    -- 12.09.2004
SELECT CONVERT(varchar(10), GETDATE(), 105)    -- 12-09-2004
SELECT CONVERT(varchar(10), GETDATE(), 106)    -- 12 09 2004
SELECT CONVERT(varchar(10), GETDATE(), 107)    -- 09 12, 2004

-- 僅時間
SELECT CONVERT(varchar(8), GETDATE(), 108)     -- 11:06:08
SELECT CONVERT(varchar(12), GETDATE(), 114)    -- 11:06:08.177

最佳實踐建議

1. 參數化查詢：應用層傳入 DateTime 物件，避免字串格式問題
2. 範圍查詢替代等值：WHERE fielddate >= '2008-02-27' AND fielddate < '2008-02-28' 可用索引，無需 CONVERT
3. 計算欄位 + 索引：SQL 2000+ 支援持久化計算欄位 date_only AS CONVERT(date, datetime_col) 建索引，查詢直接用 date_only
4. 避免欄位端函數：CONVERT(field) = ... 導致掃描；改寫為 field >= ... AND field < ... 利用索引