iT邦幫忙

DAY 8
9

T-SQL 應用之美系列 第 8

如何找出某個日期區間內的資料

當資料表中的欄位是 DATETIME 或 SMALLDATETIME 時,有可能需要找出特定區間內的資料,這篇文章便是要討論如何正確找到所需的資料。
在先前分享的文章中,有邦友問到在什麼時候才需要用到找出特定日期。一般來說,當資料表中,有日期或時間的資料時,就有可能要找出某個區間內的資料,這時候先前所分享的那些計算特定日期的技巧就派上用場了。例如,要找出某個特定的月份、日或是年份中,第一筆、最後一筆資料,或是某個區間內的資料。

這次我們就是要找出某個特定區間內的資料,為了方便解說,假設在資料表 TabTest 中,有下列的資料:

如果你需要測試的話,可以直接用下面的程式碼建立資料表並新增相關的資料:

USE tempdb
GO

CREATE TABLE TabTest(
	[出貨日期] [datetime] NOT NULL)
GO

SET NOCOUNT ON

INSERT INTO TabTest VALUES('2008-01-13 00:00:00.000')
INSERT INTO TabTest VALUES('2008-01-23 08:08:08.000')
INSERT INTO TabTest VALUES('2008-01-31 00:00:00.000')
INSERT INTO TabTest VALUES('2008-02-05 00:00:00.000')
INSERT INTO TabTest VALUES('2008-02-05 23:59:00.997')
INSERT INTO TabTest VALUES('2008-02-06 00:00:00.000')
INSERT INTO TabTest VALUES('2008-03-08 21:55:00.997')
INSERT INTO TabTest VALUES('2008-03-14 23:59:00.997')
INSERT INTO TabTest VALUES('2008-03-16 10:00:00.000')
INSERT INTO TabTest VALUES('2008-03-28 18:58:00.000')

假設現在要找出 2008 年 2 月 5 日的資料,大家可能會使用下面這樣的 T-SQL:

SELECT * FROM TabTest
	WHERE 出貨日期 = '20080205'

執行結果:

不對哦!明明 2008 年 2 月 5 日的資料應該要有 2 筆,怎麼只會找出 1 筆呢?
哦!原來是因為出貨日期欄位的資料精確到「毫秒」,好吧,那我們也精確到「毫秒」好了:

SELECT * FROM tabtest
	WHERE 出貨日期 BETWEEN '20080205 00:00:00.000' AND
                          '20080205 23:59:59.999'

執行結果:

奇怪了,怎會多出一筆 2008 年 2 月 6 日的資料啊?這樣的結果也不對啊!
由於 Microsoft SQL Server 對於時間的精確度可以高達**「一千分之三秒」,所以結束的時間不能用 23:59:59.999,要改用 23:59:59.997,這樣才能避免 SQL Server 把 20080205 23:59:59.999** 變成 20080206 00:00:00.000

SELECT * FROM TabTest
	WHERE 出貨日期 BETWEEN '20080205 00:00:00.000' AND
                          '20080205 23:59:59.997'

執行結果:

如果不用 BETWEEN,可以使用先前分享過的方法,在查詢條件式中,以「日」為單位,先算出特定日期的午夜時間(這個是所要包含的時間區間之下限),然後計算出翌日的午夜時間(這個是不包含的時間區間之上限):

SELECT * FROM tabtest
	WHERE 出貨日期 >= DATEADD(day, DATEDIFF(day, '', '20080205'), '')
	AND   出貨日期 <  DATEADD(day, DATEDIFF(day, '', '20080205') + 1, '')

希望透過這次的分享,讓大家更瞭解為什麼先前要介紹那些找出特定日期的文章!


上一篇
如何使用 T-SQL 計算年齡
下一篇
如何使用 CAST 與 CONVERT 格式化日期與時間資料
系列文
T-SQL 應用之美30

2 則留言

0
ziagopan
iT邦新手 5 級 ‧ 2008-10-07 10:27:46

我有一個簡便直覺的做法,可以分享一下:就是另外加一個 compute 欄位,將日期〈如上例的「出貨日期」〉轉為八碼文字的格式,公式如下:
(convert(varchar,datepart(year,[出貨日期])))+Right((convert(varchar,0)+convert(varchar,datepart(month,[出貨日期]))),2)+Right((convert(varchar,0)+convert(varchar,datepart(day,[出貨日期]))),2)
接下來只要使用 ='20081001' 等方式下查詢條件即可

alexc iT邦高手 1 級 ‧ 2008-10-08 16:10:53 檢舉

謝謝分享
是否要多用一個計算(Compute)欄位儲存來另外一種格式的「出貨日期」牽扯到正規話、資料表的設計...等因素
這已經超出我所訂定的主題與這篇分享的原意

0
jamesjan
iT邦高手 1 級 ‧ 2008-10-07 10:46:03

可以有更快的方法喔

&lt;pre class="c" name="code">replace(convert(varchar(10),[出貨日期],111),'/','')

^_^

我要留言

立即登入留言