本文章分享 DateTime 欄位的 Filter 方式，不在資料欄位上使用Function去Filter。
前陣子看到同事 Apple 寫一個Filter 某一天的SQL，常常會遇到這種查詢條件，所以就記錄下來。
1.準備測試資料
USE tempdb
GO
-- drop table tblDates;
CREATE TABLE tblDates
(
c1 INT,
c2 DATETIME
)
GO
INSERT INTO dbo.tblDates( c1, c2 ) VALUES (0, '2014-01-06 00:00:00');
INSERT INTO dbo.tblDates( c1, c2 ) VALUES (1, '2014-01-06 00:00:01');
INSERT INTO dbo.tblDates( c1, c2 ) VALUES (2, '2014-01-06 01:00:01');
INSERT INTO dbo.tblDates( c1, c2 ) VALUES (3, '2014-01-06 23:59:59');
INSERT INTO dbo.tblDates( c1, c2 ) VALUES (4, '2014-01-07 00:00:00');
INSERT INTO dbo.tblDates( c1, c2 ) VALUES (5, '2014-01-07 11:00:00');
INSERT INTO dbo.tblDates( c1, c2 ) VALUES (6, '2014-01-07 23:59:59');
INSERT INTO dbo.tblDates( c1, c2 ) VALUES (7, '2014-01-08 23:59:59');
INSERT INTO dbo.tblDates( c1, c2 ) VALUES (8, '2014-01-09 00:00:01');
SELECT * FROM dbo.tblDates;
2.取得 2014-01-06 的資料，傳入的參數為 2014-01-06 14:12:01
2.1.取出2014-01-06的開始及結束時間
-- 取得 2014-01-06 的資料
DECLARE @filterDate VARCHAR(32);
-- 有時傳進來的資料，並不會剛好只有日期，例如 GetDate()
SET @filterDate = '2014-01-06 14:12:01';
-- 因為 DateTime，所以比較時，要包含時間進去，所以先取出開始及結束時間
SELECT DATEDIFF(dd,0,@filterDate) AS [到查詢時間的天數]
, DATEADD(dd, DATEDIFF(dd,0,@filterDate), 0) AS [開始時間]
, DATEADD(dd, DATEDIFF(dd,0,CAST(@filterDate AS DATETIME)), 1) AS [LW結束時間];
-- 如果 BETWEEN 要用的話，結束時間要減一秒，如下，
SELECT DATEDIFF(dd,0,@filterDate) AS [到查詢時間的天數]
, DATEADD(dd, DATEDIFF(dd,0,@filterDate), 0) AS [開始時間]
, DATEADD(ss, -1, DATEADD(dd, DATEDIFF(dd,0,CAST(@filterDate AS DATETIME)), 1)) AS [BW結束時間];
2.2.將開始及結束時間，放到 WHERE 去 Filter
-- 取得 2014-01-06 的資料
DECLARE @filterDate VARCHAR(32);
-- 有時傳進來的資料，並不會剛好只有日期，例如 GetDate()
SET @filterDate = '2014-01-06 14:12:01';
-- 1. >= < + DATEADD
SELECT * FROM dbo.tblDates
WHERE c2 >= DATEADD(dd, DATEDIFF(dd,0,@filterDate), 0)
AND c2 < DATEADD(dd, DATEDIFF(dd,0,CAST(@filterDate AS DATETIME)), 1);
-- 2. BETWEEN + DATEADD
SELECT * FROM dbo.tblDates
WHERE c2 BETWEEN DATEADD(dd, DATEDIFF(dd,0,@filterDate), 0)
AND DATEADD(ss, -1, DATEADD(dd, DATEDIFF(dd,0,CAST(@filterDate AS DATETIME)), 1));
-- 因為用BETWEEN 要減 1 秒
結論
知道如何取出開始及結束時間，及它的使用時機(小於 或是 BETWEEN)，即使是開始、結束不同天，也是類似的方式哦!
例如，取得 2014-01-06 ~ 2014-01-08 的資料，將2個時間套進去就可以了，如下，
-- 取得 2014-01-06 ~ 2014-01-08 的資料
DECLARE @startFilterDate VARCHAR(32);
DECLARE @endFilterDate VARCHAR(32);
-- 有時傳進來的資料，並不會剛好只有日期，例如 GetDate()
SET @startFilterDate = '2014-01-06 14:12:01';
SET @endFilterDate = '2014-01-08 23:12:01';
-- 因為 DateTime，所以比較時，要包含時間進去，所以先取出開始及結束時間
SELECT DATEADD(dd, DATEDIFF(dd,0,@startFilterDate), 0) AS [開始時間]
, DATEADD(dd, DATEDIFF(dd,0,CAST(@endFilterDate AS DATETIME)), 1) AS [LW結束時間];
-- 如果 BETWEEN 要用的話，結束時間要減一秒，如下，
SELECT DATEADD(dd, DATEDIFF(dd,0,@startFilterDate), 0) AS [開始時間]
, DATEADD(ss, -1, DATEADD(dd, DATEDIFF(dd,0,CAST(@endFilterDate AS DATETIME)), 1)) AS [BW結束時間];
-- 1. >= < + DATEADD
SELECT * FROM dbo.tblDates
WHERE c2 >= DATEADD(dd, DATEDIFF(dd,0,@startFilterDate), 0)
AND c2 < DATEADD(dd, DATEDIFF(dd,0,CAST(@endFilterDate AS DATETIME)), 1);
-- 2. BETWEEN + DATEADD
SELECT * FROM dbo.tblDates
WHERE c2 BETWEEN DATEADD(dd, DATEDIFF(dd,0,@startFilterDate), 0)
AND DATEADD(ss, -1, DATEADD(dd, DATEDIFF(dd,0,CAST(@endFilterDate AS DATETIME)), 1));
-- 因為用BETWEEN 要減 1 秒
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
