資料庫技巧—SQL Server日期函數的巧妙用法

 

對於開發人員來說，日期處理或許簡單，或許很難。結合自己過往的開發經驗並整合網上的例子，總結出一些日期相關的操作，供自己備用及為大家分享：
 
注意：所有系統日期和時間值均得自運行 SQL Server 執行個體的電腦的作業系統。
每一種日期類型有其使用範圍，當然以剛好適用為最佳選擇，切記不要為了方便，什麼都用datetime類型。從效能方面會有很大影響，舉個例子：
一個表，有1億行的資料，有10列日期型（對於一些曆史表來說這是完全有可能的）。如果全部使用datetime，那麼光這部分的儲存空間就是：10*100000000*8位元組/(1024*1024)≈7629M≈7.4G,當然，如果有這樣的需要，再大也還是要用的，假設其實業務上不需要那麼精確（因為datetime是精確到0.00333秒），只需要精確到1分鐘即可，那麼毫不猶豫使用smalldatetime，可以減少一半的空間，也就是大約3.7G。減少空間的好處有很多，比如備份及資料庫檔案的大小可以減少，讓有限的預算做更多的事情。而且資料頁固定8KB，越少的體積單頁能存放的資料也就越多，查詢時要訪問的頁面就更少，緩解I/O壓力。同時對索引的使用也更有效，等等。
所以這裡就能體現出“設計”的重要性。

常用日期處理案例：這是文章的重點，因為上面大部分內容都可以從聯機叢書中查到
給定某個日期，計算相關的值，目前我的工作中遇到比較多的就是這些，至於有些特殊曆法所需日期，目前沒遇到，所以沒總結：
--定義給定的一天
DECLARE @Date DATETIME = GETDATE();
 
SELECT @Date AS '目前時間'
,DATEADD(DD,-1,@Date) AS '前一天'
,DATEADD(DD,1,@Date) AS '後一天'
/*月計算*/
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'--在SQL Server中0 代表1900-01-01,通過月運算，保證日恒久為1號
,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精確到天)'--找到下月初再扣減1天，建議使用DATEADD而不要直接“-1”
,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精確到datetime的小數位)'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天'
,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最後一天'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天'
,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) AS '下月最後一天'
/*周計算*/
,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(周日)'--注意此處與@@datefirst的值有關
,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'--注意此處與@@datefirst的值有關
,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'--注意此處與@@datefirst的值有關,其他天數類推
,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上個星期第一天(周日)'--注意此處與@@datefirst的值有關
,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下個星期第一天(星期日)'--注意此處與@@datefirst的值有關
,DATENAME(WEEKDAY,@Date) AS '本日是周幾'
,DATEPART(WEEKDAY,@Date) AS '本日是周幾'--傳回值 1-星期日，2-星期一，3-星期二......7-星期六
/*年度計算*/
,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末'
,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初'
,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末'
/*季度計算*/
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初'
,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'

建議：
建立時間維度表：在本人以前工作中，經常需要查詢時間範圍（精確到天），此時，可以建立一個表，每一行對應一天，然後其他列就是所需日期，比如季初季末、月初月末、年初年末甚至上年下年等。以供直接調用，並且就算存10年的資料，也就3000多條。有這樣需求的可以考慮使用。
 
速查手冊：日期往往要轉換成字元型再進行處理，所以這裡貼出部分轉換結果
Select CONVERT(varchar(100), GETDATE(), 0)--05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1)--05/16/06
Select CONVERT(varchar(100), GETDATE(), 2)--06.05.16
Select CONVERT(varchar(100), GETDATE(), 3)--16/05/06
Select CONVERT(varchar(100), GETDATE(), 4)--16.05.06
Select CONVERT(varchar(100), GETDATE(), 5)--16-05-06
Select CONVERT(varchar(100), GETDATE(), 6)--16 05 06
Select CONVERT(varchar(100), GETDATE(), 7)--05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8)--10:57:46
Select CONVERT(varchar(100), GETDATE(), 9)--05 16 200610:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10)--05-16-06
Select CONVERT(varchar(100), GETDATE(), 11)--06/05/16
Select CONVERT(varchar(100), GETDATE(), 12)--060516
Select CONVERT(varchar(100), GETDATE(), 13)--16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14)--10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20)--2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21)--2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22)--05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23)--2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24)--10:57:47
Select CONVERT(varchar(100), GETDATE(), 25)--2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100)--05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101)--05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102)--2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103)--16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104)--16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105)--16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106)--16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107)--05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108)--10:57:49
Select CONVERT(varchar(100), GETDATE(), 109)--05 16 200610:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110)--05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111)--2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112)--20060516
Select CONVERT(varchar(100), GETDATE(), 113)--16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114)--10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120)--2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121)--2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126)--2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130)--18 ???? ?????? 142710:57:49:907AM
Select CONVERT(varchar(100), GETDATE(), 131)--18/04/142710:57:49:920AM

原文連結：http://topic.csdn.net/u/20120613/01/ac7d8d4f-1aa6-4bf5-aeda-d31eccf33fcb.html