2014年2月5日 星期三

[SQL Server] 日期運算的小技巧整理(以起迄日期結束日期為例)

Reference:http://www.dotblogs.com.tw/dotjum/archive/2008/08/17/4926.aspx

在平常開發當中,會常使用到 TSQL 日期函數 , 
這邊 Dotjum 就把一些日期運算,常用的函式做些整理與應用, 
透過小範例的方式,來跟大家分享。 
首先先來看範例中的資料表格式

1SELECT FROM   dbo.test1
2SELECT getdate() AS 目前時間
image
在這個範例資料表中,可以看到有六筆資料,測試目前時間定義為 2008/8/17 15:27, 
而範例要做的是,將到期的資料不要顯示出來,這邊可以注意第二筆資料, 
可能會遇到的問題是,目前已經是 2008/8/17 15:27 ,如果你直接使用 EndDate >= getdate() 
當條件來做比對,那比對將會是 2008/8/17 00:00:00 >= 2008/8/17 15:2730 , 
可想而知,結果就是被過濾掉,所以直接使用 getDate() ,請千萬不要這樣做比對。 
 
1SELECT getdate()
2SELECT FROM dbo.test1 WHERE EndDate >= getdate()
image 
 
而要解決這個問題,可以透過一些函式來加以處理,就介紹函式的方式來解決這個問題。 

一.使用 DateDiff 的方式: 
依照SQL 2005線上文件的說明, 
DATEDIFF 函數會計算日期部份中,您指定的兩個日期中第二個與第一個日期之間的期間。 
換句話說,它會找尋兩個日期之間的間隔。 
結果是一個帶正負號的整數值,等於日期部份中的 date2 - date1。 
所以可以用天數的差距的方式,來做比對條件,

1-- 使用 DateDiff 的方式
2SELECT FROM dbo.test1 WHERE DateDiff(Day,EndDate,getdate()) <= 0
3 
4-- 在這個範例中可以看到相減後求出日期間格
5SELECT EndDate,DateDiff(Day,EndDate,getdate())FROM dbo.test1
如果不是很瞭解 DateDiff 的結果,可以透過第二個資料表的第二個欄位來知道, 
透過兩個日期相減所求出的間隔天數,可以拿來做過濾的參數, 
使用DateDiff 的方式,就成功解決前面提到當天的問題,當天及未過期的資料也顯示出來。
image
二. 使用 Convert 把日期參數轉為字串 
在最前面提到,問題的發生是因為 2008/8/17 00:00:00 >= 2008/8/17 15:2730 <--- 這個是 getDate(), 
所以有另一種方法就是把 getDate() 後面的 時分秒  都轉成 0 ,而這個有兩種作法, 
第一種作法使用 DateAdd 函式,而DateAdd是根據在指定日期中加入間隔來傳回新的 datetime 值。 
所以先透過 DateDiff 換算出目前時間與 1900-01-01 差多少天,在使用 DateAdd 加入差距的天數, 
因為是以DAY為換算單位,所以最後求出來的值就會是 2008/8/17 00:00:00 , 
所以就可以直接用 >= 來做比對,只是用先轉成字串及使用 DateAdd
1-- 使用 Convert 把日期參數轉為字串後時間都變為 0
2SELECT FROM dbo.test1
3WHERE EndDate >= Convert(varchar(16),DateAdd(Day, DateDiff(Day, 0, getdate()), 0),120)
4--
5SELECT Convert(varchar(16),DateAdd(Day, DateDiff(Day, 0, getdate()), 0),120)
6--DateAdd(以天為單位,DateDiff(以天為單位,0是1900-01-01,目前時間),由1900-01-01 加上天數)
7select getdate(), DateAdd(Day, DateDiff(Day, 0, getdate()), 0)
8SELECT  datediff(d, 0, getdate())
image
看完第一種Convert作法後,一定覺得太麻煩,所以第二種作法也是使用 Convert , 
但直接做截斷時分秒,透過年月日跟目的欄位做比對。 
透過這樣的方法,也可以查出當天及未過期的資料也顯示出來。
1SELECT Convert(varchar(10),getDate(),120)
2SELECT FROM dbo.test1
3WHERE EndDate >= Convert(varchar(10),getDate(),120)
image 
 

當然除了上述的三種方法外,還可以使用 DatePart 來去與 年月日 做比較, 
而在類似這樣範例當中,Dotjum認為 DataDiff 與 Convert 的方式,比較好來處理, 
或許大家還知道其他的方法,就互相指導一下。

沒有留言:

張貼留言