在平常開發當中，會常使用到 TSQL 日期函數 ，
這邊 Dotjum 就把一些日期運算，常用的函式做些整理與應用，
透過小範例的方式，來跟大家分享。
首先先來看範例中的資料表格式
這邊 Dotjum 就把一些日期運算，常用的函式做些整理與應用，
透過小範例的方式，來跟大家分享。
首先先來看範例中的資料表格式
1 | SELECT * FROM dbo.test1 |
2 | SELECT getdate() AS 目前時間 |
在這個範例資料表中，可以看到有六筆資料，測試目前時間定義為 2008/8/17 15:27，
而範例要做的是，將到期的資料不要顯示出來，這邊可以注意第二筆資料，
可能會遇到的問題是，目前已經是 2008/8/17 15:27 ，如果你直接使用 EndDate >= getdate()
當條件來做比對，那比對將會是 2008/8/17 00:00:00 >= 2008/8/17 15:2730 ，
可想而知，結果就是被過濾掉，所以直接使用 getDate() ，請千萬不要這樣做比對。
而範例要做的是，將到期的資料不要顯示出來，這邊可以注意第二筆資料，
可能會遇到的問題是，目前已經是 2008/8/17 15:27 ，如果你直接使用 EndDate >= getdate()
當條件來做比對，那比對將會是 2008/8/17 00:00:00 >= 2008/8/17 15:2730 ，
可想而知，結果就是被過濾掉，所以直接使用 getDate() ，請千萬不要這樣做比對。
1 | SELECT getdate() |
2 | SELECT * FROM dbo.test1 WHERE EndDate >= getdate() |
而要解決這個問題，可以透過一些函式來加以處理，就介紹函式的方式來解決這個問題。
一.使用 DateDiff 的方式：
依照SQL 2005線上文件的說明，
DATEDIFF 函數會計算日期部份中，您指定的兩個日期中第二個與第一個日期之間的期間。
換句話說，它會找尋兩個日期之間的間隔。
結果是一個帶正負號的整數值，等於日期部份中的 date2 - date1。
所以可以用天數的差距的方式，來做比對條件，
一.使用 DateDiff 的方式：
依照SQL 2005線上文件的說明，
DATEDIFF 函數會計算日期部份中，您指定的兩個日期中第二個與第一個日期之間的期間。
換句話說，它會找尋兩個日期之間的間隔。
結果是一個帶正負號的整數值，等於日期部份中的 date2 - date1。
所以可以用天數的差距的方式，來做比對條件，
1 | -- 使用 DateDiff 的方式 |
2 | SELECT * FROM dbo.test1 WHERE DateDiff(Day,EndDate,getdate()) <= 0 |
3 |
4 | -- 在這個範例中可以看到相減後求出日期間格 |
5 | SELECT EndDate,DateDiff(Day,EndDate,getdate())FROM dbo.test1 |
如果不是很瞭解 DateDiff 的結果，可以透過第二個資料表的第二個欄位來知道，
透過兩個日期相減所求出的間隔天數，可以拿來做過濾的參數，
使用DateDiff 的方式，就成功解決前面提到當天的問題，當天及未過期的資料也顯示出來。
透過兩個日期相減所求出的間隔天數，可以拿來做過濾的參數，
使用DateDiff 的方式，就成功解決前面提到當天的問題，當天及未過期的資料也顯示出來。
二. 使用 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
在最前面提到，問題的發生是因為 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 |
2 | SELECT * FROM dbo.test1 |
3 | WHERE EndDate >= Convert(varchar(16),DateAdd(Day, DateDiff(Day, 0, getdate()), 0),120) |
4 | -- |
5 | SELECT Convert(varchar(16),DateAdd(Day, DateDiff(Day, 0, getdate()), 0),120) |
6 | --DateAdd(以天為單位,DateDiff(以天為單位,0是1900-01-01,目前時間),由1900-01-01 加上天數） |
7 | select getdate(), DateAdd(Day, DateDiff(Day, 0, getdate()), 0) |
8 | SELECT datediff(d, 0, getdate()) |
看完第一種Convert作法後，一定覺得太麻煩，所以第二種作法也是使用 Convert ，
但直接做截斷時分秒，透過年月日跟目的欄位做比對。
透過這樣的方法，也可以查出當天及未過期的資料也顯示出來。
但直接做截斷時分秒，透過年月日跟目的欄位做比對。
透過這樣的方法，也可以查出當天及未過期的資料也顯示出來。
1 | SELECT Convert(varchar(10),getDate(),120) |
2 | SELECT * FROM dbo.test1 |
3 | WHERE EndDate >= Convert(varchar(10),getDate(),120) |
當然除了上述的三種方法外，還可以使用 DatePart 來去與 年月日 做比較，
而在類似這樣範例當中，Dotjum認為 DataDiff 與 Convert 的方式，比較好來處理，
或許大家還知道其他的方法，就互相指導一下。
沒有留言:
張貼留言