SQL Server 日期相關

　　對于開發人員來說，日期處理或許簡單，或許很難。結合自己過往的開發經驗并整合網上的例子，總結出一些日期相關的操作，供自己備用及為大家分享： 　　一、日期類型： 　　對于SQL Server 2008 來說(因為2000甚至2005已經稍微有被淘汰的跡象，所以在此不作過多說明，加上自己工作使用的是2008R2。所以不保證08以前的能用)，日期類型有： 數據類型 格式 范圍 精確度 存儲大?。ㄒ宰止潪閱挝唬? 用戶定義的秒的小數精度 時區偏移量 time hh:mm:ss[. nnnnnnn] 00:00:00.0000000 到 23:59:59.9999999 100 納秒 3 到 5 是 否 date YYYY-MM-DD 0001-01-01 到 9999-12-31 1 天 3 無 無 smalldatetime YYYY-MM-DD hh:mm:ss 1900-01-01 到 2079-06-06 1 分鐘 4 無 無 datetime YYYY-MM-DD hh:mm:ss[. nnn] 1753-01-01 到 9999-12-31 0.00333 秒 8 無 否 datetime2 YYYY-MM-DD hh:mm:ss[. nnnnnnn] 0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999 100 納秒 6 到 8 有 無 datetimeoffset YYYY-MM-DD hh:mm:ss[. nnnnnnn] [+|-]hh:mm 0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999（以 UTC 時間表示） 100 納秒 8 到 10 有 有 　　注意：所有系統日期和時間值均得自運行 SQL Server 實例的計算機的操作系統。 　　每一種日期類型有其使用范圍，當然以剛好適用為最佳選擇，切記不要為了方便，什么都用datetime類型。從性能方面會有很大影響，舉個例子： 　　一個表，有1億行的數據，有10列日期型(對于一些歷史表來說這是完全有可能的)。如果全部使用datetime，那么光這部分的存儲空間就是：10*100000000*8字節/(1024*1024)≈7629M≈7.4G,當然，如果有這樣的需要，再大也還是要用的，假設其實業務上不需要那么精確(因為datetime是精確到0.00333秒)，只需要精確到1分鐘即可，那么毫不猶豫使用smalldatetime，可以減少一半的空間，也就是大約3.7G。減少空間的好處有很多，比如備份及數據庫文件的大小可以減少，讓有限的預算做更多的事情。而且數據頁固定8KB，越少的體積單頁能存放的數據也就越多，查詢時要訪問的頁面就更少，緩解I/O壓力。同時對索引的使用也更有效，等等。 　　所以這里就能體現出“設計”的重要性。 　　二、日期函數：日期函數是處理日期的基礎，牢記日期函數能減少很多編程工作 　　精度較高的系統日期和時間函數 　　精確程度取決于運行 SQL Server 實例的計算機硬件和 Windows 版本。標注有：2012有效的是只有2012才出現的功能 函數 語法 返回值 返回數據類型 確定性 SYSDATETIME SYSDATETIME () 返回包含計算機的日期和時間的 datetime2(7) 值，SQL Server 的實例正在該計算機上運行。 時區偏移量未包含在內。 datetime2(7) 不具有確定性 SYSDATETIMEOFFSET SYSDATETIMEOFFSET ( ) 返回包含計算機的日期和時間的 datetimeoffset(7) 值，SQL Server 的實例正在該計算機上運行。 時區偏移量包含在內。 datetimeoffset(7) 不具有確定性 SYSUTCDATETIME SYSUTCDATETIME ( ) 返回包含計算機的日期和時間的 datetime2(7) 值，SQL Server 的實例正在該計算機上運行。 日期和時間作為 UTC 時間（通用協調時間）返回。 datetime2(7) 不具有確定性 　　精度較低的系統日期和時間函數 函數 語法 返回值 返回數據類型 確定性 CURRENT_TIMESTAMP CURRENT_TIMESTAMP 返回包含計算機的日期和時間的 datetime2(7) 值，SQL Server 的實例正在該計算機上運行。 時區偏移量未包含在內。 datetime 不具有確定性 GETDATE GETDATE ( ) 返回包含計算機的日期和時間的 datetime2(7) 值，SQL Server 的實例正在該計算機上運行。 時區偏移量未包含在內。 datetime 不具有確定性 GETUTCDATE GETUTCDATE ( ) 返回包含計算機的日期和時間的 datetime2(7) 值，SQL Server 的實例正在該計算機上運行。 日期和時間作為 UTC 時間（通用協調時間）返回。 datetime 不具有確定性 　　用來獲取日期和時間部分的函數 函數 語法 返回值 返回數據類型 確定性 DATENAME DATENAME ( datepart , date ) 返回表示指定日期的指定 datepart 的字符串。 nvarchar 不具有確定性 DATEPART DATEPART ( datepart , date ) 返回表示指定 date 的指定 datepart 的整數。 int 不具有確定性 DAY DAY ( date ) 返回表示指定 date 的“日”部分的整數。 int 具有確定性 MONTH MONTH ( date ) 返回表示指定 date 的“月”部分的整數。 int 具有確定性 YEAR YEAR ( date ) 返回表示指定 date 的“年”部分的整數。 int 具有確定性 　　用來從部件中獲取日期和時間值的函數 函數 語法 返回值 返回數據類型 確定性 DATEFROMPARTS（2012有效） DATEFROMPARTS ( year, month, day ) 返回表示指定年、月、日的 date 值。 date 具有確定性 DATETIME2FROMPARTS（2012有效） DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds,fractions, precision) 對指定的日期和時間返回 datetime2 值（具有指定精度）。 datetime2(precision) 具有確定性 DATETIMEFROMPARTS（2012有效） DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds,milliseconds ) 為指定的日期和時間返回 datetime 值。 datetime 具有確定性 DATETIMEOFFSETFROMPARTS（2012有效） DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute,seconds, fractions, hour_offset, minute_offset, precision ) 對指定的日期和時間返回 datetimeoffset值，即具有指定的偏移量和精度。 datetime(precision) 具有確定性 SMALLDATETIMEFROMPARTS（2012有效） SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ) 為指定的日期和時間返回 smalldatetime值。 smalldatetime 具有確定性 TIMEFROMPARTS（2012有效） TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) 對指定的時間返回 time 值（具有指定精度）。 time(precision) 具有確定性 　　用來獲取日期和時間差的函數 函數 語法 返回值 返回數據類型 確定性 DATEDIFF DATEDIFF ( datepart , startdate , enddate ) 返回兩個指定日期之間所跨的日期或時間 datepart 邊界的數目。 int 具有確定性 　　用來修改日期和時間值的函數 函數 語法 返回值 返回數據類型 確定性 DATEADD DATEADD ( datepart , number , date ) 通過將一個時間間隔與指定 date 的指定 datepart 相加，返回一個新的 datetime 值。 date 參數的數據類型。 具有確定性 EOMONTH（2012） EOMONTH ( start_date [,month_to_add ] ) 返回包含指定日期的月份的最后一天（具有可選偏移量）。 返回類型為 start_date 的類型或datetime2(7)。 具有確定性 SWITCHOFFSET SWITCH OFFSET ( DATETIMEOFFSET, time_zone) SWITCH OFFSET 更改 DATETIMEOFFSET 值的時區偏移量并保留 UTC 值。 datetimeoffset 具有其小數精度：DATETIMEOFFSET 具有確定性 TODATETIMEOFFSET TODATETIMEOFFSET ( expression ,time_zone) TODATETIMEOFFSET 將 datetime2 值轉換為 datetimeoffset 值。 datetime2 值被解釋為指定 time_zone 的本地時間。 具有 datetime 參數的小數精度的datetimeoffset 具有確定性 　　用來設置或獲取會話格式的函數 函數 語法 返回值 返回數據類型 確定性 @@DATEFIRST @@DATEFIRST 返回對會話進行 SET DATEFIRST 操作所得結果的當前值。 tinyint 不具有確定性 SET DATEFIRST SET DATEFIRST { number | @number_var } 將一周的第一天設置為從 1 到 7 的一個數字。 不適用 不適用 SET DATEFORMAT SET DATEFORMAT { format | @format_var } 設置用于輸入 datetime 或 smalldatetime 數據的日期各部分（月/日/年）的順序。 不適用 不適用 @@LANGUAGE @@LANGUAGE 返回當前使用的語言的名稱。 @@LANGUAGE 不是日期或時間函數。但是，語言設置會影響日期函數的輸出。 不適用 不適用 SET LANGUAGE SET LANGUAGE { [ N ] 'language' | @language_var } 設置會話和系統消息的語言環境。 SET LANGUAGE 不是日期或時間函數。 但是，語言設置會影響日期函數的輸出。 不適用 不適用 sp_helplanguage sp_helplanguage [ [ @language = ]'language' ] 返回有關所有支持語言的日期格式的信息。 sp_helplanguage 不是日期或時間存儲過程。 但是，語言設置會影響日期函數的輸出。 不適用 不適用 　　用來驗證日期和時間值的函數 函數 語法 返回值 返回數據類型 確定性 ISDATE ISDATE (expression ) 確定 datetime 或 smalldatetime 輸入表達式是否為有效的日期或時間值。 int 只有與 CONVERT 函數一起使用，同時指定了 CONVERT 樣式參數且樣式不等于 0、100、9 或 109 時，ISDATE 才是確定的。 　　三、日期操作詳解： 　　3.1、注意：SQL Server 將 0 解釋為 1900 年 1 月 1 日。 　　3.2、對于一些當前會話需要臨時改變日期設置時，可以使用SET關鍵字改變： 　　SET DATEFIRST { number | @number_var }： 　　將一周的第一天設置為從 1 到 7 的一個數字。7為默認的第一天，即周日。查看 SET DATEFIRST 的當前設置，請使用 @@DATEFIRST函數。注意此值是在運行時執行 　　SET DATEFORMAT{format|@format_var}： 　　設置用于解釋 date、smalldatetime、datetime、datetime2 和 datetimeoffset 字符串的月、日和年日期部分的順序。有效參數為 mdy、 dmy、 ymd、 ydm、 myd 和 dym默認值為 mdy。SETDATEFORMAT 將覆蓋 SETLANGUAGE 的隱式日期格式設置。 　　設置會話語言：雖然這里是語言，但是會影響日期的格式： 　　SET LANGUAGE Italian; 　　GO 　　SELECT @@DATEFIRST; 　　GO 　　SET LANGUAGE us_english; 　　GO 　　SELECT @@DATEFIRST; 　　3.3、常用函數操作： 函數及其參數 描述 DAY ( date ) 返回一個整數，該整數表示指定的 date 是該月份的哪一天，DAY 與 DATEPART ( day、 date) 返回相同的值。 如果 date 只包含時間部分，則返回值為 1，即基準日 YEAR ( date ) 返回一個整數，它表示指定 date 的年份, YEAR 與 DATEPART ( year, date) 返回相同的值。如果 date 僅包含一個時間部分，則返回值為 1900，即基準年 CURRENT_TIMESTAMP 與getdate()相同 DATENAME (datepart, date ) 返回表示指定 date 的指定 datepart 的字符串，DATENAME 可用于選擇列表 WHERE、HAVING、GROUP BY 和 ORDER BY 子句中 DATEDIFF (datepart, startdate , enddate ) 返回指定的 startdate 和 enddate 之間所跨的指定 datepart 邊界的計數（帶符號的整數）。 DATEADD (datepart , number , date ) 指定 number 時間間隔（有符號整數）與指定 date 的指定 datepart 相加后，返回該 date ISDATE ( expression ) 如果 expression 是有效的 date、 time 或 datetime 值，則返回 1；否則，返回 0 SWITCHOFFSET （DATETIMEOFFSET, time_zone ) 返回從存儲的時區偏移量變為指定的新時區偏移量時得到的 datetimeoffset 值 　　四、常用日期處理案例：這是文章的重點，因為上面大部分內容都可以從聯機叢書中查到 　　給定某個日期，計算相關的值，目前我的工作中遇到比較多的就是這些，至于有些特殊歷法所需日期，目前沒遇到，所以沒總結： 　　[sql] view plaincopyprint? 　　--定義給定的一天 　　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 '下季季末' 　　--定義給定的一天 　　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多條。有這樣需求的可以考慮使用。 　　六、速查手冊：日期往往要轉換成字符型再進行處理，所以這里貼出部分轉換結果 　　[sql] view plaincopyprint? 　　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://www.anti-gravitydesign.com