原帖出處：
http://blog.csdn.net/dba_huangzj/article/details/7657979
對于開發(fā)人員來說，日期處理或許簡單，或許很難。結(jié)合自己過往的開發(fā)經(jīng)驗并整合網(wǎng)上的例子，總結(jié)出一些日期相關(guān)的操作，供自己備用及為大家分享：
?
一、日期類型：
對于SQL Server 2008 來說（因為2000甚至2005已經(jīng)稍微有被淘汰的跡象，所以在此不作過多說明，加上自己工作使用的是2008R2。所以不保證08以前的能用），日期類型有：
|
數(shù)據(jù)類型 |
格式 |
范圍 |
精確度 |
存儲大小（以字節(jié)為單位） |
用戶定義的秒的小數(shù)精度 |
時區(qū)偏移量 |
|
? |
hh:mm:ss[. nnnnnnn] |
00:00:00.0000000 到 23:59:59.9999999 |
100 納秒 |
3 到 5 |
是 |
否 |
|
? |
YYYY-MM-DD |
0001-01-01 到 9999-12-31 |
1 天 |
3 |
無 |
無 |
|
? |
YYYY-MM-DD hh:mm:ss |
1900-01-01 到 2079-06-06 |
1 分鐘 |
4 |
無 |
無 |
|
? |
YYYY-MM-DD hh:mm:ss[. nnn] |
1753-01-01 到 9999-12-31 |
0.00333 秒 |
8 |
無 |
否 |
|
? |
YYYY-MM-DD hh:mm:ss[. nnnnnnn] |
0001-01-01 00:00:00.0000000 到 9999-12-31 23:59:59.9999999 |
100 納秒 |
6 到 8 |
有 |
無 |
|
? |
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 |
有 |
有 |
注意：所有系統(tǒng)日期和時間值均得自運行 SQL Server 實例的計算機的操作系統(tǒng)。
每一種日期類型有其使用范圍，當(dāng)然以剛好適用為最佳選擇，切記不要為了方便，什么都用datetime類型。從性能方面會有很大影響，舉個例子：
一個表，有1億行的數(shù)據(jù)，有10列日期型（對于一些歷史表來說這是完全有可能的）。如果全部使用datetime，那么光這部分的存儲空間就是：10*100000000*8字節(jié)/(1024*1024)≈7629M≈7.4G,當(dāng)然，如果有這樣的需要，再大也還是要用的，假設(shè)其實業(yè)務(wù)上不需要那么精確（因為datetime是精確到0.00333秒），只需要精確到1分鐘即可，那么毫不猶豫使用smalldatetime，可以減少一半的空間，也就是大約3.7G。減少空間的好處有很多，比如備份及數(shù)據(jù)庫文件的大小可以減少，讓有限的預(yù)算做更多的事情。而且數(shù)據(jù)頁固定8KB，越少的體積單頁能存放的數(shù)據(jù)也就越多，查詢時要訪問的頁面就更少，緩解I/O壓力。同時對索引的使用也更有效，等等。
所以這里就能體現(xiàn)出“設(shè)計”的重要性。
二、日期函數(shù)：日期函數(shù)是處理日期的基礎(chǔ)，牢記日期函數(shù)能減少很多編程工作
精度較高的系統(tǒng)日期和時間函數(shù)
精確程度取決于運行 SQL Server 實例的計算機硬件和 Windows 版本。標(biāo)注有：2012有效的是只有2012才出現(xiàn)的功能
|
函數(shù) |
語法 |
返回值 |
返回數(shù)據(jù)類型 |
確定性 |
|
? |
SYSDATETIME () |
返回包含計算機的日期和時間的? datetime2(7) ?值，SQL Server 的實例正在該計算機上運行。 時區(qū)偏移量未包含在內(nèi)。 |
datetime2(7) |
不具有確定性 |
|
? |
SYSDATETIMEOFFSET ( ) |
返回包含計算機的日期和時間的? datetimeoffset(7) ?值，SQL Server 的實例正在該計算機上運行。 時區(qū)偏移量包含在內(nèi)。 |
datetimeoffset(7) |
不具有確定性 |
|
? |
SYSUTCDATETIME ( ) |
返回包含計算機的日期和時間的? datetime2(7) ?值，SQL Server 的實例正在該計算機上運行。 日期和時間作為 UTC 時間（通用協(xié)調(diào)時間）返回。 |
datetime2(7) |
不具有確定性 |
精度較低的系統(tǒng)日期和時間函數(shù)
|
函數(shù) |
語法 |
返回值 |
返回數(shù)據(jù)類型 |
確定性 |
|
? |
CURRENT_TIMESTAMP |
返回包含計算機的日期和時間的? datetime2(7) ?值，SQL Server 的實例正在該計算機上運行。 時區(qū)偏移量未包含在內(nèi)。 |
datetime |
不具有確定性 |
|
? |
GETDATE ( ) |
返回包含計算機的日期和時間的? datetime2(7) ?值，SQL Server 的實例正在該計算機上運行。 時區(qū)偏移量未包含在內(nèi)。 |
datetime |
不具有確定性 |
|
? |
GETUTCDATE ( ) |
返回包含計算機的日期和時間的? datetime2(7) ?值，SQL Server 的實例正在該計算機上運行。 日期和時間作為 UTC 時間（通用協(xié)調(diào)時間）返回。 |
datetime |
不具有確定性 |
用來獲取日期和時間部分的函數(shù)
|
函數(shù) |
語法 |
返回值 |
返回數(shù)據(jù)類型 |
確定性 |
|
? |
DATENAME (? datepart ?,? date ?) |
返回表示指定日期的指定? datepart ?的字符串。 |
nvarchar |
不具有確定性 |
|
? |
DATEPART (? datepart ?,? date ?) |
返回表示指定? date ?的指定? datepart ?的整數(shù)。 |
int |
不具有確定性 |
|
? |
DAY (? date ?) |
返回表示指定? date ?的“日”部分的整數(shù)。 |
int |
具有確定性 |
|
? |
MONTH (? date ?) |
返回表示指定? date ?的“月”部分的整數(shù)。 |
int |
具有確定性 |
|
? |
YEAR (? date ?) |
返回表示指定? date ?的“年”部分的整數(shù)。 |
int |
具有確定性 |
用來從部件中獲取日期和時間值的函數(shù)
|
函數(shù) |
語法 |
返回值 |
返回數(shù)據(jù)類型 |
確定性 |
|
? |
DATEFROMPARTS (? year ,? month ,? day ?) |
返回表示指定年、月、日的? date ?值。 |
date |
具有確定性 |
|
? |
DATETIME2FROMPARTS (? year ,? month ,? day ,? hour ,? minute ,? seconds , fractions ,? precision ) |
對指定的日期和時間返回? datetime2 ?值（具有指定精度）。 |
datetime2( precision ) |
具有確定性 |
|
? |
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 (? hour ,? minute ,? seconds ,? fractions ,? precision ?) |
對指定的時間返回? time ?值（具有指定精度）。 |
time( precision ) |
具有確定性 |
用來獲取日期和時間差的函數(shù)
|
函數(shù) |
語法 |
返回值 |
返回數(shù)據(jù)類型 |
確定性 |
|
? |
DATEDIFF (? datepart ?,? startdate ?,? enddate ?) |
返回兩個指定日期之間所跨的日期或時間? datepart ?邊界的數(shù)目。 |
int |
具有確定性 |
用來修改日期和時間值的函數(shù)
|
函數(shù) |
語法 |
返回值 |
返回數(shù)據(jù)類型 |
確定性 |
|
? |
DATEADD (? datepart ?,? number? ,? date ?) |
通過將一個時間間隔與指定? date ?的指定? datepart ?相加，返回一個新的? datetime ?值。 |
date ?參數(shù)的數(shù)據(jù)類型。 |
具有確定性 |
|
? |
EOMONTH (? start_date ?[, month_to_add ?] ) |
返回包含指定日期的月份的最后一天（具有可選偏移量）。 |
返回類型為? start_date ?的類型或 datetime2(7) 。 |
具有確定性 |
|
? |
SWITCH? OFFSET ?(? DATETIMEOFFSET ,? time_zone ) |
SWITCH? OFFSET ?更改 DATETIMEOFFSET 值的時區(qū)偏移量并保留 UTC 值。 |
datetimeoffset ?具有其小數(shù)精度： DATETIMEOFFSET |
具有確定性 |
|
? |
TODATETIMEOFFSET (? expression? , time_zone ) |
TODATETIMEOFFSET 將 datetime2 值轉(zhuǎn)換為 datetimeoffset 值。 datetime2 值被解釋為指定 time_zone 的本地時間。 |
具有? datetime ?參數(shù)的小數(shù)精度的 datetimeoffset |
具有確定性 |
用來設(shè)置或獲取會話格式的函數(shù)
|
函數(shù) |
語法 |
返回值 |
返回數(shù)據(jù)類型 |
確定性 |
|
? |
@@DATEFIRST |
返回對會話進行 SET DATEFIRST 操作所得結(jié)果的當(dāng)前值。 |
tinyint |
不具有確定性 |
|
? |
SET DATEFIRST {? number | @ number_var ?} |
將一周的第一天設(shè)置為從 1 到 7 的一個數(shù)字。 |
不適用 |
不適用 |
|
? |
SET DATEFORMAT {? format | @ format_var ?} |
設(shè)置用于輸入? datetime ?或? smalldatetime ?數(shù)據(jù)的日期各部分（月/日/年）的順序。 |
不適用 |
不適用 |
|
? |
@@LANGUAGE |
返回當(dāng)前使用的語言的名稱。 @@LANGUAGE 不是日期或時間函數(shù)。但是，語言設(shè)置會影響日期函數(shù)的輸出。 |
不適用 |
不適用 |
|
? |
SET LANGUAGE { [ N ]? ' language ' | @ language_var ?} |
設(shè)置會話和系統(tǒng)消息的語言環(huán)境。 SET LANGUAGE 不是日期或時間函數(shù)。 但是，語言設(shè)置會影響日期函數(shù)的輸出。 |
不適用 |
不適用 |
|
? |
sp_helplanguage ?[ [? @language = ?] ' language ' ?] |
返回有關(guān)所有支持語言的日期格式的信息。 sp_helplanguage ?不是日期或時間存儲過程。 但是，語言設(shè)置會影響日期函數(shù)的輸出。 |
不適用 |
不適用 |
用來驗證日期和時間值的函數(shù)
|
函數(shù) |
語法 |
返回值 |
返回數(shù)據(jù)類型 |
確定性 |
|
? |
ISDATE ( expression ?) |
確定? datetime ?或? smalldatetime ?輸入表達式是否為有效的日期或時間值。 |
int |
只有與 CONVERT 函數(shù)一起使用，同時指定了 CONVERT 樣式參數(shù)且樣式不等于 0、100、9 或 109 時，ISDATE 才是確定的。 |
?
三、日期操作詳解：
3.1、注意：SQL Server 將 0 解釋為 1900 年 1 月 1 日。
3.2、對于一些當(dāng)前會話需要臨時改變?nèi)掌谠O(shè)置時，可以使用SET關(guān)鍵字改變：
SET DATEFIRST { number | @number_var }：
將一周的第一天設(shè)置為從 1 到 7 的一個數(shù)字。7為默認的第一天，即周日。查看 SET DATEFIRST 的當(dāng)前設(shè)置，請使用? @@DATEFIRST 函數(shù)。注意此值是在運行時執(zhí)行
SET DATEFORMAT{format|@format_var}：
?? 設(shè)置用于解釋 date、smalldatetime、datetime、datetime2 和 datetimeoffset 字符串的月、日和年日期部分的順序。有效參數(shù)為 mdy、 dmy、 ymd、 ydm、 myd 和 dym默認值為 mdy。SETDATEFORMAT 將覆蓋? SETLANGUAGE ?的隱式日期格式設(shè)置。
設(shè)置會話語言：雖然這里是語言，但是會影響日期的格式：
SET LANGUAGE Italian;
GO
SELECT @@DATEFIRST;
GO
SET LANGUAGE us_english;
GO
SELECT @@DATEFIRST;
???????? 3.3、常用函數(shù)操作：
|
函數(shù)及其參數(shù) |
描述 |
|
DAY ( date ) |
返回一個整數(shù)，該整數(shù)表示指定的 date 是該月份的哪一天，DAY 與? DATEPART ?( day、 date) 返回相同的值。 如果 date 只包含時間部分，則返回值為 1，即基準(zhǔn)日 |
|
YEAR ( date ) |
返回一個整數(shù)，它表示指定 date 的年份, YEAR 與? DATEPART ?( year, date) 返回相同的值。如果 date 僅包含一個時間部分，則返回值為 1900，即基準(zhǔn)年 |
|
CURRENT_TIMESTAMP |
與getdate()相同 |
|
DATENAME (datepart, date ) |
返回表示指定 date 的指定 datepart 的字符串，DATENAME 可用于選擇列表 WHERE、HAVING、GROUP BY 和 ORDER BY 子句中 |
|
DATEDIFF (datepart, startdate , enddate ) |
返回指定的 startdate 和 enddate 之間所跨的指定 datepart 邊界的計數(shù)（帶符號的整數(shù)）。 |
|
DATEADD (datepart , number , date ) |
指定 number 時間間隔（有符號整數(shù)）與指定 date 的指定 datepart 相加后，返回該 date |
|
ISDATE ( expression ) |
如果 expression 是有效的 date 、? time ? 或 datetime 值，則返回 1；否則，返回 0 |
|
SWITCHOFFSET （DATETIMEOFFSET, time_zone ) |
返回從存儲的時區(qū)偏移量變?yōu)橹付ǖ男聲r區(qū)偏移量時得到的 datetimeoffset 值 |
?
?
四、常用日期處理案例：這是文章的重點，因為上面大部分內(nèi)容都可以從聯(lián)機叢書中查到
給定某個日期，計算相關(guān)的值，目前我的工作中遇到比較多的就是這些，至于有些特殊歷法所需日期，目前沒遇到，所以沒總結(jié)：
--定義給定的一天
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的小數(shù)位)'
,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的值有關(guān)
,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'--注意此處與@@datefirst的值有關(guān)
,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'--注意此處與@@datefirst的值有關(guān),其他天數(shù)類推
,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上個星期第一天(周日)'--注意此處與@@datefirst的值有關(guān)
,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下個星期第一天(星期日)'--注意此處與@@datefirst的值有關(guān)
,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 '下季季末'
?
五、建議：
創(chuàng)建 時間維度表 ：在本人以前工作中，經(jīng)常需要查詢時間范圍（精確到天），此時，可以創(chuàng)建一個表，每一行對應(yīng)一天，然后其他列就是所需日期，比如季初季末、月初月末、年初年末甚至上年下年等。以供直接調(diào)用，并且就算存10年的數(shù)據(jù)，也就3000多條。有這樣需求的可以考慮使用。
?
六、速查手冊：日期往往要轉(zhuǎn)換成字符型再進行處理，所以這里貼出部分轉(zhuǎn)換結(jié)果
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://blog.csdn.net/dba_huangzj/article/details/7657979
?
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
