SQL Server 日期時間查詢技巧全攻略：格式轉換、區間查詢、Hibernate 參數、毫秒精度

1. 日期時間格式化輸出 (CONVERT)

SELECT CONVERT(VARCHAR, GetDate(), 120);  -- 2024-01-15 14:30:45
SELECT CONVERT(CHAR(10), GetDate(), 120); -- 2024-01-15
-- 常用 style: 112 (YYYYMMDD), 103 (DD/MM/YYYY), 108 (HH:MM:SS)

2. SSMS 查詢視窗直接比較 (無需函數)

SELECT * FROM Info
WHERE DataTime > '2012-11-11 00:00:00'
  AND DataTime < '2012-12-12 00:00:00';

SQL Server 會自動將符合 ISO 8601 (YYYY-MM-DDTHH:MM:SS) 的字串隱含轉換為 DATETIME。

3. Hibernate / ORM 參數綁定 (必須傳 Date 物件)

// HQL
FROM Person p WHERE p.date = :date

// Java
query.setParameter("date", new java.util.Date()); // 或 Timestamp
// 切勿傳字串，否則會報「轉換錯誤」

4. 毫秒精度陷阱

• DATETIME 精度約 3.33 毫秒 (儲存值為 0.000, 0.003, 0.007...)。
• DATETIME2 (SQL 2008+) 精度 100 奈秒，建議新系統使用。
• Java Calendar.getTime() 只能到毫秒，若需更高精度改用 Instant / LocalDateTime。

5. 日期區間查詢正確寫法 (含當日結尾)

錯誤：WHERE DateCol BETWEEN '2011-11-11' AND '2012-12-12' — 會漏掉 12/12 當天 00:00:00 之後的資料。
正確：

WHERE DateCol >= '2011-11-11'
  AND DateCol <  DATEADD(DAY, 1, '2012-12-12'); -- 含 12/12 全天

或使用 CAST(DateCol AS DATE) = '2012-12-12' (SQL 2008+)。

6. 相對時間常用範本

-- 今日
WHERE CAST(CreatedAt AS DATE) = CAST(GETDATE() AS DATE)

-- 近 7 天
WHERE CreatedAt >= DATEADD(DAY, -7, CAST(GETDATE() AS DATE))

-- 本月
WHERE CreatedAt >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)
  AND CreatedAt <  DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1))

7. 避免隱含轉換導致索引失效

永遠將參數/常數轉換為欄位型別，而非對欄位做函數運算：

-- 好：索引可用
WHERE CreatedAt >= @StartDate AND CreatedAt < @EndDate

-- 差：掃描全表
WHERE CONVERT(DATE, CreatedAt) = @SomeDate