SQL Server如何進行時間比較的代碼

標籤：

例子: datediff(dd,add_time,getdate()) not between 0 and 7

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)
的值.通過判定相減後值的正負來判定日期的先後。

SQL Server如何進行時間比較的代碼