一． 數據類型
MS SQL Server 2005有datetime和smalldatetime兩種格式的日期時間數據類型。注意：沒有單獨的日期或時間類型。
datetime
數據庫內部用兩個4字節的整數存儲datetime數據類型的值。第一個4字節存儲基礎日期（即1900-1-1, base date）之前或之後的日期。日期范圍為1753-1-1至9999-12-31。當日期為1900-1-1時值為0；之前的日期是負數；之後日期是正數。另外一個4字節存儲以午夜後三又三分之一（10/3）毫秒數所代表的每天的時間。精確度為百分之三秒（等於3.33毫秒或0.00333秒）。如下表所示，把值調整到.000、.003、或.007秒的增量。
查詢中，我們常常需要搜索指定日期范圍內的數據，比如返回1998-01-01當天內的數據，你可能會這樣寫：
Date >= '1998-01-01 00:00:00.000'and date <= '1998-01-01 23:59:59.999'
根據上面的調整規則，其實這句語句的實際搜索范圍為：
Date >= '1998-01-01 00:00:00.000' and date <= '1998-01-02 00:00:00.000'
你會看到這包括了1998-01-02的數據，所以最好的正確的搜索語句為：
Date >= '1998-01-01 00:00:00.000'and date < '1998-01-02 00:00:00.000'
我們可以使用下面的代碼來顯示datetime類型是如何保存的（下面的smalldatetime方法同）：
declare @dt datetime
set @dt = getdate()
select convert(int, substring(convert(varbinary(8), @dt), 1, 4)), datediff(day, 0, @dt)
select convert(int, substring(convert(varbinary(8), @dt), 5, 8)), datediff(ms, dateadd(day, datediff(day, 0, @dt), 0), @dt) * 3 / 10.0
smalldatetime
smalldatetime數據類型存儲日期和每天的時間，但精確度低於datetime。SQL Server將smalldatetime的值存儲為兩個2字節的整數。第一個2字節存儲1900-1-1後的天數。另外一個2字節存儲午夜後的分鐘數。日期范圍從1900-1-1到2079-6-6，時間精確到分鐘。29.998秒或更低的smalldatetime值向下捨入為最接近的分鐘，29.999秒或更高的smalldatetime值向上捨入為最接近的分鐘。
--returns time as 12:35
SELECT CAST('2000-05-08 12:35:29.998' AS smalldatetime)
GO
--returns time as 12:36
SELECT CAST('2000-05-08 12:35:29.999' AS smalldatetime)
GO
二． 日期時間的賦值
上面說了格式，當然我們是沒有辦法直接賦整數給日期時間變量的。給這些變量賦值通常是給它指定個一定格式的字符串。SQL Server會自動將字符串轉換成日期格式保存的，注意：數據庫中是不會保存數據格式的字符串的。下面幾種日期格式的字符串，SQL Server會非常輕易就“認出”的。
1） ISO 8061格式
ISO時間格式：yyyy-mm-ddT hh:mi:ss[.mmm]，必須指定每一個元素，只有毫秒是可選的，時間成分以24小時格式指定。
使用 ISO 8601 格式的優勢在於它是一個國際標准。另外，使用此格式指定的日期時間值很明確。同時，此格式不受 SET DATEFORMAT 設置或 SET LANGUAGE 設置的影響。
2） 字母日期格式
在 Microsoft SQL Server 2005 中可以以當前語言給出的月的全名（如 April）或月的縮寫（如 Apr）來指定日期數據；逗號是可選的，而且忽略大小寫。
這種日期格式只有在制定的語言中才能起作用。我們可以通過調用存儲過程sp_helplanguage來查看SQL Server支持的所有語言以及這些語言的月份全名和簡稱。我們同時可以發現有三種語言：簡體中文，韓語（한국어）和日語（日本語）的月份名稱是數字而不是字母，因此在這三種語言中是不支持字母日期格式的。
以下是使用字母日期格式的一些原則：
a.把日期和時間數據括在單引號中 (')。
b.下面是 SQL Server 日期數據的有效字母格式（括號內的字符是可選字符）：
Apr[il] [15][,] 1996
Apr[il] 15[,] [19]96
Apr[il] 1996 [15]
[15] Apr[il][,] 1996
15 Apr[il][,][19]96
15 [19]96 apr[il]
[15] 1996 apr[il]
1996 APR[IL] [15]
1996 [15] APR[IL]
注意 ，沒有這樣一種情況：省略日，縮寫年份。如 Apr[il] [15][,] [19]96 這是錯誤的格式。
c.如果只指定年份的最後兩位數字，則小於 [兩位數年份截止]配置選項值最後兩位數字的值與縮略形式的年份位於同一個世紀。大於或等於該選項值的值位於縮略形式年份的上一世紀。例如，如果[兩位數年份截止]為 2050（默認值），則 25 被解釋為 2025，50 被解釋為 1950。為避免模糊不清，請使用四位數的年份。
[兩位數年份截止]配置選項服務器屬性中的高級配置下。該選項可以從1753 到 9999 之間指定一個整數來表示縮略形式的年份，以將兩位數的年份解釋為四位數的年份。
Microsoft SQL Server 默認的時間范圍是 1950-2049，表示截止年份為 2049。這說明 SQL Server 將兩位數年份 49 解釋為 2049 年，將兩位數年份 50 解釋為 1950 年，而將兩位數年份 99 解釋為 1999 年。若要維護向後兼容性，請將設置保持為默認值。
d.如果沒有指定日，則默認值為當月第一天。
e.當按字母形式指定月時，SET DATEFORMAT 會話設置不起作用。
3） ODBC日期時間格式
ODBC API 定義了轉義序列來表示日期和時間值，ODBC 稱之為時間戳數據。用於 SQL Server 的 Microsoft OLE DB 訪問接口所支持的 OLE DB 語言定義 (DBGUID-SQL) 也支持這種 ODBC 時間戳格式。使用 ADO、OLE DB 和基於 ODBC 的 API 的應用程序可以使用這種 ODBC 時間戳格式來表示日期和時間。
ODBC 時間戳的轉義序列格式為：{ literal_type 'constant_value' }
literal_type 指定轉義序列的類型。時間戳有三個 literal_type 說明符：
d = 僅日期
t = 僅時間
ts = 時間戳（時間 + 日期）
'constant_value'
轉義序列的值。constant_value 必須遵循每個 literal_type 的格式。
literal_type constant_value 格式
d yyyy-mm-dd
t hh:mm:ss[.fff]
ts yyyy-mm-dd hh:mm:ss[.fff]
這些是 ODBC 時間和日期常量的例子：
{ ts '1998-05-02 01:23:56.123' }
{ d '1990-10-02' }
{ t '13:33:41' }
不要混淆 ODBC 和 OLE DB 時間戳數據類型名稱與 Transact-SQL timestamp 數據類型名稱。ODBC 和 OLE DB 時間戳數據類型記錄日期和時間。Transact-SQL timestamp 數據類型是一個與時間值無關的二進制數據類型。
4） 分隔字符串格式
在 Microsoft SQL Server 2005 中，可以使用指定的數值月指定日期數據。例如，5/20/97 代表 1997 年 5 月 20 日。使用數值日期格式時，可在字符串中使用斜槓 (/)、連字符 (-) 或句點 (.) 作為分隔符指定月、日和年。此字符串必須使用以下格式：
number separator number separator number [time] [time]
這種格式常常會因為不同國家可能解釋不同，比如像“01/02/03”這個日期格式：
像這種情況，我們若要直接賦值給日期變量，我們就可以使用SET DATEFORMAT來設置格式：參數包括 mdy、dmy、ymd、ydm、myd 和 dym。每種語言都有自己的缺省DATEFORMAT，可以通過sp_helplanguage來查看每種語言的日期格式。
下列是分隔字符串日期格式有效例子：
[0]4/15/[19]96 -- (mdy)
[0]4-15-[19]96 -- (mdy)
[0]4.15.[19]96 -- (mdy)
[0]4/[19]96/15 -- (myd)
15/[0]4/[19]96 -- (dmy)
15/[19]96/[0]4 -- (dym)
[19]96/15/[0]4 -- (ydm)
[19]96/[0]4/15 -- (ymd)
5） 未分隔字符串格式
Microsoft SQL Server 2005 允許您將日期數據指定為未分隔字符串。日期數據能夠用 4、6 或 8 位數字、空字符串或不帶日期值的時間值來指定。
SET DATEFORMAT會話設置並不適用於全數值日期項，例如不帶分隔符的數值項。6 位或 8 位數字的字符串始終被解釋為 ymd。月和日必須始終是 2 位數字。
下面是有效的未分隔字符串格式：[19]960415
只有 4 位數字的字符串被解釋為年。月和日期被設置為 1 月 1 日。當只指定 4 位數字時，必須包括世紀。
6） 時間部分格式
上面說的日期格式重點談了日期部分的格式，若日期格式的時間部分沒有定義，那麼SQL Server就將子夜（midnight）時間作為默認的時間值。
現在來看看時間部分的格式。Microsoft SQL Server 2005 能夠識別下列格式的時間數據。用單引號 (') 把每一種格式括起來。
14:30
14:30[:20:999]
14:30[:20.9]
4am
4 PM
[0]4[:30:20:500]AM
如果日期部分沒有進行定義，那麼SQL Server就將1900年1月1日作為默認的日期值。
可以用一個 AM 或 PM 後綴來表明時間值是在中午 12 點之前還是之後。AM 或 PM 的大小寫可忽略。
小時可以用 12 小時或 24 小時的時鐘來指定。小時值解釋如下：
a. 小時值 0 表示午夜 (AM) 後的小時，不論是否指定了 AM。當小時值等於 0 時，不能指定 PM。
b. 如果未指定 AM 或 PM，小時值 1 到 11 表示中午以前的小時。當指定 AM 時，也表示中午以前的小時。當指定 PM 時，則表示中午以後的小時。
c. 如果未指定 AM 或 PM，小時值 12 表示始於中午的小時。如果指定 AM，則表示始於午夜的小時。如果指定 PM，則表示始於中午的小時。例如：12:01 是指中午過後 1 分鐘，即 12:01 PM，而 12:01 AM 是指午夜過後 1 分鐘。指定 12:01 AM 與指定 00:01 或 00:01 AM 相同。
d. 如果未指定 AM 或 PM，小時值 13 到 23 表示中午以後的小時。當指定 PM 時，也表示中午以後的小時。當小時值從 13 到 23 時，不能指定 AM。
e. 小時值 24 無效，用 12:00 AM 或 00:00 表示午夜。
可以在毫秒之前加上冒號 (:) 或者句號 (.)。如果前面加冒號，這個數字表示千分之一秒。如果前面加句號，單個數字表示十分之一秒，兩個數字表示百分之一秒，三個數字表示千分之一秒。例 如，12:30:20:1 表示 12:30 過了 20 又千分之一秒；12:30:20.1 表示 12:30 過了 20 又十分之一秒。
上面說了SQL Server可以“自動識別”的所有日期時間類型。識別時可能要考慮當前的語言（試用set lanuage設置）和日期格式（試用set dateformat設置）的影響。你若厭倦於此，可以使用最後的“殺手锏”——CONVERT函數來顯式轉換，CONVERT的第三個參數對於日期格式和字符串格式定義如下：
三． 日期的輸出
和日期的賦值不同，SQL Server不會“自動”識別你需要哪種輸出格式。我們只有試用CONVERT函數來實現日期輸出格式的控制。
當使用CONVERT函數處理日期時間的輸出格式時，我們可以使用與處理輸入數據時完全相同的風格設置，唯一的區別是：處理輸出數據格式時，CONVERT函數將一個日期時間實例轉換為一個字符串，而處理輸入數據格式時，則是從一個字符串生成一個日期時間實例。
四． 時間函數
常用的時間函數如下：
DATEADD：可以對時間類型的指定部分進行加減計算。我們常常根據一個時間來構造出另外一個時間，比如下個月的今天,本月底等等，我們應該也盡量使用DATEADD函數來構造，它可以避免一些閏月、年底、月底之類的錯誤。對一個日期時間變量直接加減一個整數和使用DATEADD(DAY, n,@D)的功能是一樣的。
DATEDIFF：該函數對兩個時間變量對指定部分進行比較計算。此函數不考慮比指定日期部分更高的粒度級別，它只考慮更低級別的部分。對時間的比較應盡量使用本函數。使用DATEDIFF和DATEADD可以對日期時間變量進行“截尾”的操作（舉例見下面的常用查詢）。
DATEPART：返回日期時間變量的指定部分的值。
DATENAME：返回日期時間變量的指定部分的值，和DATAPART不同的是本函數返回的是個字符串類型。
GETDATE()返回本機器的當前時間。CURRENT_TIMESTAMP變量與本函數功能相同。
GETUTCDATE()返回本機器的當前UTC（格林尼治標准）時間。
DATEADD、DATEDIFF、DATEPART和DATENAME函數使用到一些共同的參數與縮寫如下：
利用上面的函數，總結一些常用的查詢（可以看看是如何進行“截尾”操作的）：
--本月開始，相當於將本月的日期“截尾”
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
--本年開始，相當於將本年的月份“截尾”
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
--本周一，相當於將本周的日期“截尾”
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0)
--本季度開始，相當於將本季的日期“截尾”
SELECT DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0)
--本月結束
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) -1
--本年結束
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) - 1
--本周結束
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()) + 1, 0) - 1
--本季度結束
SELECT DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()) + 1, 0) - 1
--足年計算法一.
使用PUBS數據庫中的EMPLOYEE表。（下同）
SELECT *, DATEDIFF(YEAR, HIRE_DATE, GETDATE()) - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, HIRE_DATE, GETDATE()), HIRE_DATE) > GETDATE() THEN 1
ELSE 0
END
FROM EMPLOYEE
--此法對於閏年的2月29日和平年的2月28日是相等的。
--足年計算法二.
SELECT *, DATEDIFF(YEAR, HIRE_DATE, GETDATE()) - CASE WHEN 100 * MONTH(HIRE_DATE) + DAY(HIRE_DATE) > 100 * MONTH(GETDATE()) + DAY(GETDATE()) THEN 1
ELSE 0
END
FROM EMPLOYEE
SELECT *, DATEDIFF(YEAR, HIRE_DATE, GETDATE()) - CASE WHEN SUBSTRING(CONVERT(VARCHAR(8), HIRE_DATE, 112), 5, 4) > SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112), 5, 4) THEN 1
ELSE 0
END, SUBSTRING(CONVERT(VARCHAR(8), HIRE_DATE, 112), 5, 4), SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112), 5, 4)
FROM EMPLOYEE
此法對於閏年的2月29日和平年的3月1日是相等的。
足年計算法三.
SELECT *, DATEDIFF(YEAR, HIRE_DATE, GETDATE()) - CASE WHEN DATEPART(DAYOFYEAR, HIRE_DATE) > DATEPART(DAYOFYEAR, GETDATE()) THEN 1
ELSE 0
END
FROM EMPLOYEE
--此法比較簡單，但是在閏年和平年3月份以後的日期時有1日的差別。
--足月計算法一.
SELECT *, DATEDIFF(MONTH, HIRE_DATE, GETDATE()) - CASE WHEN DATEADD(MONTH, DATEDIFF(MONTH, HIRE_DATE, GETDATE()), GETDATE()) > GETDATE() THEN 1
ELSE 0
END
FROM EMPLOYEE
--足月計算法二.
SELECT *, DATEDIFF(MONTH, HIRE_DATE, GETDATE()) - CASE WHEN DAY(HIRE_DATE) > DAY(GETDATE()) THEN 1
ELSE 0
END
FROM EMPLOYEE
參看書籍：
1. 《SQL Server 2005編程藝術》
2. 《SQL Server 2005技術內幕: T-SQL程序設計》
3. http://technet.microsoft.com/zh-cn/library/ms180878%28SQL.90%29.ASPx
4. http://technet.microsoft.com/zh-cn/library/ms191004%28SQL.90%29.ASPx
