SQL資料運算基礎(中級)10

操作日期和時間

日期和時間函數對建立一個站點是非常有用的。站點的主人往往對一個表中的資料何時被更新感興趣。透過日期和時間函數，你可以在毫秒級追蹤一個表的改變。

返回當前日期和時間

透過函數GETDATE（），你可以獲得目前的日期和時間。例如，語句SELECT GETDATE()回傳如下的結果：

……………………………..

NOV 30 1997 3:29AM

(1 row(s) affected)

顯然，如果你將來使用這個函數，你得到的日期將比這個時間晚，或者梗早。

函數GETDATE（）可以用來作為DATEDIME（）型欄位的缺省值。這對插入記錄時保存當時的時間是有用的。例如，假設有一個表格用來保存你網站上的活動日誌。每當有訪客造訪到你的網站時，就在表格中新增記錄，記下訪客的名字，活動，和進行造訪的時間。要建立一個表，其中的記錄包含有當前的日期和時間，可以添加一個DATETIME型字段，指定其缺省值為函數GETDATE（）的返回值，就像這樣：

CREATE TABLE site_log (

username VARCHAR(40),

useractivity VARCHAR(100),

entrydate DATETIME DEFAULT GETDATE())

轉換日期和時間

你也許已經注意到，在上一節的例子中，函數GETDATE（）的回傳值在顯示時只顯示到秒。實際上，SQL Sever內部時間可以精確到毫秒級（確切地說，可以精確到3.33毫秒）。

要得到不同格式的日期和時間，你需要使用函數CONVERT（）。例如，當下面的這個語句執行時，顯示的時間將包括毫秒：

SELECT CONVERT(VARCHAR(30),GETDATE(),9)

注意例子中數字9的使用。這個數字指明了在顯示日期和時間時使用哪種日期和時間格式。當這個語句執行時，將顯示如下的日期和時間：

…………………………………..

Nov 30 1997 3:29:55:170AM

(1 row(s) affected)

在函數CONVERT（）中你可以使用許多種不同風格的日期和時間格式。表11.1顯示了所有的格式。

表11.1 日期和時間的類型

類型值 標準 輸出

0 Default mon dd yyyy hh:miAM

1 USA mm/dd/yy

2 ANSI yy.mm.dd

3 British/French dd/mm/yy

4 German dd.mm.yy

5 Italian dd-mm-yy

6 - dd mon yy

7 - mon dd,yy

8 - hh:mi:ss

Default + milliseconds--mon dd yyyy
hh:mi:ss:mmmAM(or )

10 USA mm-dd-yy

11 JAPAN yy/mm/dd

12 ISO yymmdd

13 Europe Default + milliseconds--dd mon yyyy

hh:mi:ss:mmm(24h)

14 - hh:mi:ss:mmm(24h)

型0，9，和13總是返回四位的年。對其它類型，要顯示世紀，把style值加上100。類型13和14返回24小時時鐘的時間。類型0，7，和13返回的月份用三位字符表示（用Nov代表November）.

對表11.1中所列的每一種格式，你可以把類型值加上100來顯示有世紀的年（例如，00年將顯示為2000年）。例如，要以日本標準顯示日期，包含世紀，你應使用以下的語句：

SELECT CONVERT（VARCHAR（30），GETDATE（），111）

在這個例子中，函數CONVERT（）把日期格式轉換，顯示為1997/11/30

抽取日期和時間

在許多情況下，你也許只想得到日期和時間的一部分，而不是完整的日期和時間。例如，假設你想要列出你的網站目錄中每個網站被查詢的月份。這時你不希望完整的日期和時間把網頁弄亂。為了抽取日期的特定部分，你可以使用函數DATEPART（），像這樣：

SELECT site_name ‘Site Name’,

DATEPART(mm,site_entrydate) ‘Month Posted’ FROM site_directory

函數DATEPART（）的參數是兩個變數。第一個變數指定要抽取日期的哪一部分；第二個變數是實際的資料。在這個例子中，函數DATEPART（）抽取月份，因為mm代表月份。下面是這個SELECT 語句的輸出結果：

Site Name Month Posted

………………………………………………………………

Yahoo 2

Microsoft 5

Magicw3 5

(3 row(s) affected)

Month Posted列顯示了每個網站被查詢的月份。函數DATEPART（）的回傳值是一個整數。你可以用這個函數抽取日期的各個不同部分，如表​​11.2所示。

表11.2 日期的各部分及其簡寫

日期部分 簡寫 值

year yy 1753--999​​9

quarter QQ 1--4

month mm 1--12

day of year dy 1--366

day dd 1--31

week wk 1--53

weekday dw 1--7(Sunday--Saturday)

hour hh 0--23

minute mi 0--59

second ss 0--59

milisecond ms 0--999​​

當你需要進行日期和時間的比較時，使用函數DATEPART（）返回整數是有用的。但是，上例的查詢結果（2，5）不是十分易讀。要以更易讀的格式得到部分的日期和時間，你可以使用函數DATENAME（），如下例所示：

SELECT site_name ‘Site Name’

DATENAME(mm,site_entrydate) ‘Month Posted’

FROM site_directory

函數DATENAME（）和函數DATEPART（）接收相同的參數。但是，它的回傳值是一個字串，而不是一個整數。以下是上例該用DATENAME（）得到的結果：

Site Name Month Postec

…………………………………………………………………….

Yahoo February

Microsoft June

Magicw3 June

(3 row(s) affected)

你也可以用函數DATENAE（）來抽取一個星期中的某一天。下面的例子同時抽取一週中的某一天和日期中的月份：

SELECT site_name ‘Site Name’,

DATENAME(dw,site_entrydate)+ ‘-’ + DATENAME（mm,site_entrydate）

‘Day and Month Posted’ FORM site_directory

這個範例執行時，將傳回如下的結果：

Site Name Day and Month Posted

…………………………………………………………………………

Yahoo Friday - February

Microsoft Tuesday - June

Magicw3 Monday - June

(3 row(s) affected)

返回日期和時間範圍

當你分析表中的數據時，你也許希望取出某個特定時間的數據。你也許對特定的某一天中――比如說2000年12月25日――訪客在你站點上的活動感興趣。要取出這種類型的數據，你也許會試著使用這樣的SELECT語句：

SELECT * FROM weblog WHERE entrydate=”12/25/20000”

不要這樣做。這個SELECT語句不會傳回正確的記錄――它將只傳回日期和時間是12/25/2000 12:00:00:000AM的記錄。換句話說，只有剛好在午夜零點輸入的記錄才會被回傳。

注意：

在本節的討論中，假設字段entrydate是DATETIME型，而不是SMALLDATETIME型。本節的討論對SMALLDATETIME型欄位也是適用的，不過SMALLDATETIME型欄位只能精確到秒。

問題是SQL Sever將以完整的日期和時間來取代部分日期和時間。例如，當你輸入一個日期，但不輸入時間時，SQL Sever將加上缺省的時間「12：00：00：000AM」。當你輸入一個時間，但不輸入日期時，SQL Sever會加上預設的日期「Jan 1 1900」。

要回傳正確的記錄，你需要適用日期和時間範圍。有不只一種途徑可以做到這一點。例如，下面的這個SELECT 語句將能傳回正確的記錄：

SELECT * FROM weblog

WHERE entrydate>=”12/25/2000” AND entrydate
這個語句可以完成任務，因為它選取的是表中的日期和時間大於等於12/25/2000 12:00:00:000AM並小於12/26/2000 12:00:00:000AM的記錄。換句話說，它將正確地返回2000年聖誕節這一天輸入的每一筆記錄。

另一種方法是，你可以使用LIKE來回傳正確的記錄。透過在日期表達式中包含通配符“％”，你可以匹配一個特定日期的所有時間。這裡有一個例子：

SELECT * FROM weblog WHERE entrydate LIKE ‘Dec 25 2000%’

這個語句可以符合正確的記錄。因為通配符“％”代表了任何時間。

使用這兩種符合日期和時間範圍的函數，你可以選擇某個月，某一天，某一年，某個小時，某一分鐘，某一秒，甚至某一毫秒內輸入的記錄。但是，如果你使用LIKE 來符合秒或毫秒，你首先需要使用函數CONVERT（）把日期和時間轉換成更精確的格式（請參閱前面「轉換日期和時間」一節）。

比較日期和時間

最後，還有兩個日期和時間函數對根據日期和時間取出記錄是有用的。使用函數DATEADD（）和DATEDIFF（），你可以比較日期的早晚。例如，下面的SELECT語句將顯示表格中的每一筆記錄已經輸入了多少小時：

SELECT entrydate ‘Time Entered’

DATEDIFF(hh,entrydate,GETDATE()) ‘Hours Ago’ FROM weblog

如果目前時間是2000年11月30號下午6點15分，則會回傳如下的結果：

Time Entered Hours Ago

…………………………………………………..

Dec 30 2000 4:09PM 2

Dec 30 2000 4:13PM 2

Dec 1 2000 4:09PM 698

(3 row(s) affected)

函數DADEDIFF（）的參數是三個變數。第個變數指定日期的某一部分。在這個例子中，是按小時對日期進行比較，（要了解日期各部分的詳細內容，請參考表11.2）在日期2000年11月1日和2000年11月30日的指定時間之間有689小時。另外兩個參數是要比較的時間。為了傳回一個正數，較早的時間應該先給。

函數DATEADD（）把兩個日期相加。當你需要計算截止日期這一類的資料時，這個函數是有用處的。例如，假設訪客必須先註冊才能使用你的網站。註冊以後，他們可以免費使用你的站點一個月。要確定什麼時候他們的免費時間會用完，你可以使用以下的SELECT語句：

SELECT username ‘User Name’,

DATEADD(mm,1,firstvisit_date) ‘Registration Expires’

FROM registration_table

函數DATEADD（）的參數有三個變數。第一個變數代表日期的某一部分（參見表11.2），這個例子用到了代表月份的mm。第二個變數指定了時間的間隔――在本例中是一個月。最後一個變數是一個日期，在這個例子中，日期是取自DATETIME型欄位firstvisit_date.假設當前日期是June 30,2000，這個語句將會傳回如下的內容：

User Name Registration Expires

………………………………………………………………………………

Bill Gates Jul 30 2000 4:09PM

PResident Clinton Jul 30 2000 4:13PM

William Shakespeare Jul 1 2000 4:09PM

(3 row(s) affected)

注意：

與你預料的相反，使用函數DATEADD（）把一個日期加上一個月，它並不加上30天。這個函數只簡單地把月份值加1。這意味著在11月註冊的人將比在2月註冊的人多得到2天或3天的時間。要避免這個問題，你可以用函數DATEADD（）直接增加天數，而不是月份。

寄email

你可以用SQL Sever發送簡單的e_mail訊息。要做到這一點，你需要在你的系統中安裝郵件伺服器，例如Microsoft Exchange Sever(參見第四章「Exchange Active Sever,Index Sever,和NetShow」)。你還需要設定SQL Sever 以識別郵件伺服器。

要讓SQL Sever 能辨識郵件伺服器，啟動事務管理器並從選單中選擇Sever|SQL Mail|Configue，這時會出現一個如圖11.3所示的對話框。輸入你在郵件伺服器中註冊的使用者名稱和口令，然後按一下OK。

注意：

如果你使用Microsoft Exchange Sever,配置SQL Sever的過程將會大大不同。你需要在同一個（網域）使用者帳號下執行Microsoft SQL Sever和Exchange Sever。你還需要在安裝了SQL Sever的機器上安裝Exchange Cliect並給這個帳號建立一個一個設定檔。完成這些之後，你就可以在SQL Mail Configuration對話方塊中輸入該設定檔的名字。

圖11。3

在發送郵件之前，你要先啟動SQL Mail。從選單中選擇Sever|SQL Mail|Start。如果你的郵件伺服器配置正確，並且你輸入了正確的使用者名稱和口令，則SQL Mail會成功啟動。

注意：

你可以把SQL Sever設定為自動啟動郵件服務。要做到這一點，在Set Sever Optons對話框（從選單中選擇Sever|SQL Sever|Configure）中選擇Auto Start Mail Client即可。

要發送一個郵件，你可以使用名為xp_sendmail的擴充預存程序。這裡有一個如何使用這個過程的範例：

master..xp_sendmail “president@whitehouse.gov”,”Hello Mr. President”

這個過程呼叫向e_mail地址president@whitehouse.gov發送一個簡單的email訊息：「Hello Mr. President」。你可以用任何其它的email地址和信息取代上例中相應的內容，但是，你所發送的信息不能超過255個字符長度。

當你想隨時了解你的站點數據庫的狀態時，預存程序xp_sendmail是有用處的。總結

這一章加深了你的SQL知識。表中資料的統計資料。 SQL Sever的掌握。你將學習如何用SQL來進行程式設計，如何建立預存過程，觸發器和執行計畫。更另人興奮的是，你將學會讓SQL Sever自動建立網頁的簡單方法。

 以上就是SQL資料操作基礎(中級)10的內容，更多相關文章請關注PHP中文網（www.php.cn）！