例子:
select count(*) from table where DATEDIFF ([second], '2004-09-18 00:00:18', '2004-09-18 00:00:19') > 0
說明
select DATEDIFF(day, time1 , time2) 對應示例語句如下
select DATEDIFF(day, '2010-07-23 0:41:18', '2010-07-23 23:41:18')
time1 > time2 為負數;
time1 < time2 為正數;
[day] :只會比較 2010-07-23 忽略 0:41:18' 其他同理
以下分別:
年: SELECT DATEDIFF([year],time1 , time2) 返回值: -6 ,說明是後減前 與 mysql教程 相反的。
月: SELECT DATEDIFF([month], time1 , time2)
天: SELECT DATEDIFF([day], time1 , time2)
小時: SELECT DATEDIFF([hour], time1 , time2)
秒: SELECT DATEDIFF([second], time1 , time2)
通過函數GETDATE(),你可以獲得當前的日期和時間。函數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()中你可以使用許多種不同風格的日期和時間格式。下表顯示了所有的格式。
日期和時間的類型:
類型值 標准 輸出
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
9 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).
對表中所列的每一種格式,你可以把類型值加上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()的返回值是一個整數。你可以用這個函數抽取日期的各個不同部分,如下表所示。
日期的各部分及其簡寫
日期部分 簡寫 值
year yy 1753--9999
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()www.3ppt.com來抽取一個星期中的某一天。下面的這個例子同時抽取一周中的某一天和日期中的月份:
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的記錄。換句話說,只有剛好在午夜零點輸入的記錄才被返回。
問題是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/26/2000”
這個語句可以完成任務,因為它選取的是表中的日期和時間大於等於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()的參數有三個變量。第一個變量代表日期的某一部分,這個例子用到了代表月份的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。
用northwind庫中的employees表作為用例表。
1.between...and語句的使用:
說明:between...and用於指定測試范圍
看以下實例:
執行sql語句“SELECT hiredate FROM employees”顯示結果如下:
hiredate
1992-05-01 00:00:00.000
1992-08-14 00:00:00.000
1992-04-01 00:00:00.000
1993-05-03 00:00:00.000
1993-10-17 00:00:00.000
1993-10-17 00:00:00.000
1994-01-02 00:00:00.000
1994-03-05 00:00:00.000
1994-11-15 00:00:00.000
從以上結果集中搜索出hiredate在"1993-10-17"到"1994-01-02"的記錄,則sql語句如下:
SELECT hiredate
FROM employees
WHERE hiredate between cast(1993-10-17 as datetime) and cast(1994-01-02 as datetime)
執行該語句後結果如下:
hiredate
1993-10-17 00:00:00.000
1993-10-17 00:00:00.000
1994-01-02 00:00:00.000
以上sql語句中出現between...and和cast,其中cast是類型轉換函數:在該例中將字符串轉換成日期型值.
在WHERE中用between...and把"1993-10-17"到"1994-01-02"的記錄搜索出來.
2.兩個日期值的比較大小可以用< <= > >=運算符,和datediff函數
datediff函數:DATEDIFF ( datepart , startdate , enddate )
datepart的取值可以是year,quarter,Month,dayofyear,Day,Week,Hour,minute,second,millisecond
startdate 是從 enddate 減去。如果 startdate 比 enddate 晚,返回負值。
看以下實例:
搜索hiredate的值在"1993-05-03"後的記錄,該sql語句如下:
SELECT hiredate
FROM employees
WHERE datediff(day,cast(1993-05-03 as datetime),hiredate) >0
執行該語句後結果如下:
hiredate
1993-10-17 00:00:00.000
1993-10-17 00:00:00.000
1994-01-02 00:00:00.000
1994-03-05 00:00:00.000
1994-11-15 00:00:00.000
因此datediff(day,cast(1993-05-03 as datetime),hiredate)是把hiredate的值按"day"減去cast(1993-05-03 as datetime)
的值.通過判定相減後值的正負來判定日期的先後。