[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (2)

延續前一篇的討論
[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (1)


建議 使用 比較運算子 + 邏輯運算子

1. 使用 比較運算子 + 邏輯運算子，符合 SARG，使用到索引
2. 無須額外耗用 CONVERT/CAST 轉換資料類型
3. 直觀的語法、語意，通用性高

WHERE TransactionDate >= '20140805' AND TransactionDate < '20140806'

---

為何不建議使用  BETWEEN ?

因為 BETWEEN 在處理 DATE/TIME 資料類型時，容易造成誤解，導致查詢結果不正確。

問題：以下  WHERE 搜尋條件 是找出什麼資料？

WHERE TransactionDate BETWEEN '20140805' AND '20140806'

答案會是?

1. 20140805 當日的全部資料
2. 20140805 ~ 20140806 兩日的全部資料
3. 20140805 全日 加上 20140806 00:00:00:000 時間點的資料

用以下範例來討論

-- 01_檢視資料特性_Datetime

WHERE TransactionDate BETWEEN '20140805' AND '20140806'

卻是篩選出 20140805 當日 加上 20140806 00:00:00:000 時間點的資料
回傳：4 筆資料列，Cost：0.150749

-- 02_BETWEEN_僅回傳4_rows

預期

應該回傳 5 筆資料列，卻僅回傳 4 筆資料列，缺少 1 筆資料列：2014-08-06 07:00:00.000

以條件式 BETWEEN '20140805' AND '20140806' 為例
多半寫出這類的語句，應該要找的是包含這 兩天 的全部資料。
預期應該回傳 5 筆資料列。

卻是

但實際上
BETWEEN '20140805 00:00:00.000' AND '20140806 00:00:00.000'

也就是：20140805 全日 加上 20140806 00:00:00:000 時間點的資料。並未包含 20140806 當日的全部資料。

解決方案

該如何有效解決問題呢？

• 改用 比較運算子 + 邏輯運算子 來篩選出明確的日期時間範圍
• 例如：>= (大於或等於),  <= (小於或等於), < (小於)。

WHERE TransactionDate >= '20140805' AND TransactionDate < '20140807'

-- 03_改用比較運算子_正確回傳5_rows

---

對照執行計畫

近一步觀察執行計畫，以 BETWEEN 跟 比較運算子 +邏輯運算子 來對照，BETWEEN 是耗用更多的資源。

-- 10_對照兩者執行計畫

-- 11_BETWEEN_搜尋述詞

觀察

• 搜尋述詞-起點
• [tempdb].[dbo].[Transactions02].TransactionDate >= 純量運算子('2014-08-05 00:00:00.000')
• 搜尋述詞-終點 
• [tempdb].[dbo].[Transactions02].TransactionDate <= 純量運算子('2014-08-06 00:00:00.000')

留意

BETWEEN 的終點，卻是被解譯為：
<= 純量運算子('2014-08-06 00:00:00.000')

-- 12_BETWEEN_搜尋述詞_2

-- 13_BETWEEN_搜尋述詞_3

使用  比較運算子 + 邏輯運算子

如預期的結果正確回傳。
語意沒有被誤解。

-- 14_比較運算子_搜尋述詞

-- 15_比較運算子_搜尋述詞_2

-- 16_比較運算子_搜尋述詞_3

---

範例程式碼

20171014_WHERE_DATE_Compare_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40MDNoMGF4Wml6M00?usp=sharing

---

參考資料

[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (1)
http://sharedderrick.blogspot.tw/2017/10/sql-server-where-search-condition-with.html

What do BETWEEN and the devil have in common?
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx

It's Time for Time
http://www.kimballgroup.com/1997/07/its-time-for-time/