sql日期函數(shù)
1.一個月第一天的
Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
2.本周的星期一
Select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
3.一年的第一天
Select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
4.季度的第一天
Select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
5.當天的半夜
Select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
6.上個月的最后一天
Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
7.去年的最后一天
Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
8.本月的最后一天
Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
9.本年的最后一天
Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))
10.本月的第一個星期一
select DATEADD(wk, DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0)
返回當前日期和時間
通過函數(shù)GETDATE(),你可以獲得當前的日期和時間。函數(shù)GETDATE()可以用來作為DATEDIME型字段的缺省值。這對插入記錄時保存當時的時間是有用的。要建立一個表,其中的記錄包含有當前的日期和時間,可以添加一個DATETIME型字段,指定其缺省值為函數(shù)GETDATE()的返回值,就象這樣:
Create TABLE site_log (
username VARCHAR(40),
useractivity VARCHAR(100),
entrydate DATETIME DEFAULT GETDATE())
轉(zhuǎn)換日期和時間
函數(shù)GETDATE()的返回值在顯示時只顯示到秒。實際上,SQL Sever內(nèi)部時間可以精確到毫秒級(確切地說,可以精確到3.33毫秒)。
要得到不同格式的日期和時間,你需要使用函數(shù)CONVERT()。例如,當下面的這個語句執(zhí)行時,顯示的時間將包括毫秒:
Select CONVERT(VARCHAR(30),GETDATE(),9)
注意例子中數(shù)字9的使用。這個數(shù)字指明了在顯示日期和時間時使用哪種日期和時間格式。當這個語句執(zhí)行時,將顯示如下的日期和時間:
Nov 30 1997 3:29:55:170AM
(1 row(s) affected)
在函數(shù)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)
在這個例子中,函數(shù)CONVERT()把日期格式進行轉(zhuǎn)換,顯示為1997/11/30
抽取日期和時間
在許多情況下,你也許只想得到日期和時間的一部分,而不是完整的日期和時間。為了抽取日期的特定部分,你可以使用函數(shù)DATEPART(),象這樣:
Select site_name ‘Site Name’,
DATEPART(mm,site_entrydate) ‘Month Posted’ FROM site_directory
函數(shù)DATEPART()的參數(shù)是兩個變量。第一個變量指定要抽取日期的哪一部分;第二個變量是實際的數(shù)據(jù)。在這個例子中,函數(shù)DATEPART()抽取月份,因為mm代表月份。下面是這個Select 語句的輸出結(jié)果:
Site Name Month Posted
………………………………………………………………
Yahoo 2
Microsoft 5
Magicw3 5
(3 row(s) affected)
Month Posted列顯示了每個站點被查詢的月份。函數(shù)DATEPART()的返回值是一個整數(shù)。你可以用這個函數(shù)抽取日期的各個不同部分,如下表所示。
日期的各部分及其簡寫
日期部分 簡寫 值
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
當你需要進行日期和時間的比較時,使用函數(shù)DATEPART()返回整數(shù)是有用的。但是,上例中的查詢結(jié)果(2,5)不是十分易讀。要以更易讀的格式得到部分的日期和時間,你可以使用函數(shù)DATENAME(),如下例所示:
Select site_name ‘Site Name’
DATENAME(mm,site_entrydate) ‘Month Posted’
FROM site_directory
函數(shù)DATENAME()和函數(shù)DATEPART()接收同樣的參數(shù)。但是,它的返回值是一個字符串,而不是一個整數(shù)。下面是上例該用DATENAME()得到的結(jié)果:
Site Name Month Postec
………………………………………………………………….
Yahoo February
Microsoft June
Magicw3 June
(3 row(s) affected)
你也可以用函數(shù)DATENAE()來抽取一個星期中的某一天。下面的這個例子同時抽取一周中的某一天和日期中的月份:
Select site_name ‘Site Name’,
DATENAME(dw,site_entrydate)+ ‘-’ + DATENAME(mm,site_entrydate)
‘Day and Month Posted’ FORM site_directory
這個例子執(zhí)行時,將返回如下的結(jié)果:
Site Name Day and Month Posted
………………………………………………………………………
Yahoo Friday - February
Microsoft Tuesday - June
Magicw3 Monday - June
(3 row(s) affected)
返回日期和時間范圍
當你分析表中的數(shù)據(jù)時,你也許希望取出某個特定時間的數(shù)據(jù)。你也許對特定的某一天中――比如說2000年12月25日――訪問者在你站點上的活動感興趣。要取出這種類型的數(shù)據(jù),你也許會試圖使用這樣的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年圣誕節(jié)這一天輸入的每一條記錄。
另一種方法是,你可以使用LIKE來返回正確的記錄。通過在日期表達式中包含通配符“%”,你可以匹配一個特定日期的所有時間。這里有一個例子:
Select * FROM weblog Where entrydate LIKE ‘Dec 25 2000%’
這個語句可以匹配正確的記錄。因為通配符“%”代表了任何時間。
使用這兩種匹配日期和時間范圍的函數(shù),你可以選擇某個月,某一天,某一年,某個小時,某一分鐘,某一秒,甚至某一毫秒內(nèi)輸入的記錄。但是,如果你使用LIKE 來匹配秒或毫秒,你首先需要使用函數(shù)CONVERT()把日期和時間轉(zhuǎn)換為更精確的格式(參見前面“轉(zhuǎn)換日期和時間”一節(jié))。
比較日期和時間
最后,還有兩個日期和時間函數(shù)對根據(jù)日期和時間取出記錄是有用的。使用函數(shù)DATEADD()和DATEDIFF(),你可以比較日期的早晚。例如,下面的Select語句將顯示表中的每一條記錄已經(jīng)輸入了多少個小時:
Select entrydate ‘Time Entered’
DATEDIFF(hh,entrydate,GETDATE()) ‘Hours Ago’ FROM weblog
如果當前時間是2000年11月30號下午6點15分,則會返回如下的結(jié)果:
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)
函數(shù)DADEDIFF()的參數(shù)是三個變量。第個變量指定日期的某一部分。在這個例子中,是按小時對日期進行比較,(要了解日期各部分的詳細內(nèi)容,請參考表11.2)在日期2000年11月1日和2000年11月30日的指定時間之間有689個小時。另外兩個參數(shù)是要進行比較的時間。為了返回一個正數(shù),較早的時間應該先給。
函數(shù)DATEADD()把兩個日期相加。當你需要計算截止日期這一類的數(shù)據(jù)時,這個函數(shù)是有用處的。假如你要查詢一個月前注冊用戶的記錄,你可以使用如下的Select語句:
Select username ‘User Name’,
DATEADD(mm,1,firstvisit_date) ‘Registration Expires’
FROM registration_table
函數(shù)DATEADD()的參數(shù)有三個變量。第一個變量代表日期的某一部分,這個例子用到了代表月份的mm。第二個變量指定了時間的間隔――在本例中是一個月。最后一個變量是一個日期,在這個例子中,日期是取自DATETIME型字段firstvisit_date.假設當前日期是June 30,2000,這個語句將返回如下的內(nèi)容:
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)
注意:
使用函數(shù)DATEADD()把一個日期加上一個月,它并不加上30天。這個函數(shù)只簡單地把月份值加1。
SQL Server里面可能經(jīng)常會用到的日期格式轉(zhuǎn)換方法:
舉例如下:
select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08
select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
20040912110608
select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12
select CONVERT(varchar(12) , getdate(), 112 )
20040912
select CONVERT(varchar(12) , getdate(), 102 )
2004.09.12
select CONVERT(varchar(12) , getdate(), 101 )
09/12/2004
select CONVERT(varchar(12) , getdate(), 103 )
12/09/2004
select CONVERT(varchar(12) , getdate(), 104 )
12.09.2004
select CONVERT(varchar(12) , getdate(), 105 )
12-09-2004
select CONVERT(varchar(12) , getdate(), 106 )
12 09 2004
select CONVERT(varchar(12) , getdate(), 107 )
09 12, 2004
select CONVERT(varchar(12) , getdate(), 108 )
11:06:08
select CONVERT(varchar(12) , getdate(), 109 )
09 12 2004 1
select CONVERT(varchar(12) , getdate(), 110 )
09-12-2004
select CONVERT(varchar(12) , getdate(), 113 )
12 09 2004 1
select CONVERT(varchar(12) , getdate(), 114 )
11:06:08.177
更多文章、技術交流、商務合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
