使用DATEADD和DATEDIFF來計算SQL Server的DATETIME值

 

在SQL Server資料庫中，DATETIME和SMALLDATETIME值是以整數儲存的。然而，與整數不同的是，它們不能直接地進行數學運算。儘管如此，有時候還是需要在日期/時間值中添加或減去一個時間間隔。比如，你可能想在一值上加一些月數或天數，或者甚至可能是小時數。你甚至可能想比較兩個日期/時間值以便確定它們之間的時間間隔，如相差的天數或年數。為了簡化這些類型的計算，Transact-SQL支援兩個重要的日期/時間方法：DATEADD和DATEDIFF。

 

在關於DATETIME值這一系列文章的第四部分，我闡述了如何使用這兩個方法並舉例說明它們是如何工作的。為了示範這些方法，我使用了下面的Transact-SQL代碼在AdventureWorks樣本資料庫中建立了一個Sales.Orders表：

 

USE AdventureWorks GO IF EXISTS (SELECT table_name  FROM information_schema.tables  WHERE table_schema = 'Sales' AND table_name = 'Orders') DROP TABLE Sales.Orders GO CREATE TABLE Sales.Orders ( OrderID INT NOT NULL， OrderDate DATETIME NOT NULL， DelivDate DATETIME NOT NULL )  GO INSERT INTO Sales.Orders VALUES(1001， GETDATE()， '2008-09-08 18:27:10.750')

 

表的定義包含了OrderDate和DelivDate欄位，兩者都是DATETIME資料類型。在我建立了表之後，我在表中插入了一行用於測試DATEADD和DATEDIFF方法的資料。

 

使用DATEADD方法

 

在一些情況下，你可能想添加一個時間間隔到DATETIME或SMADDDATETIME值中——或者減去一個時間間隔。比如，你可能需要在一個指定的日期中增加或減去一個月。你可以使用DATEADD方法來執行這個計算。這個方法運用了下面的文法：

 

DATEADD(<date/time_part>， <number>， <date>)

 

<date/time_part>預留位置指的是日期/時間值中增加或減少的增量/餘差（如日或月）。下表列出了可以使用的日期/時間部分，以及代表這些部分的縮寫：

 

Date/time part	Abbreviations
year	yy， yyyy
quarter	qq， q
month	mm， m
day of year	dy， y
day	dd， d
week	wk， ww
weekday	dw
hour	hh
minute	mi， n
second	ss， s
millisecond	ms

 

比如，如果你想在一個日期/時間值中增加一小時，可以使用hh縮寫。在某些情況下，日期/時間部分支援兩個縮寫，如周可以用wk或ww支援。

 

<number>預留位置指的是所增加的數值（一個整數）。比如，如果在日期中增加10天，就是10。但是，注意，如果是減去時間間隔，它必須是一個負整數。比如，從天數中減去10，就必須是-10。

 

<date>預留位置指的是增加或減少的指定間隔的日期/時間值。它可能是一個日期/時間格式的字串值，或者是方法返回的一個日期/時間值，又或者是常見的DATETIME或SMALLDATETIME欄位。

 

讓我們舉例來說明它是如何工作的。在下面的SELECT語句中，我增加三個月到Sales.Orders表中OrderDate值：

 

SELECT OrderDate， DATEADD(mm， 3， OrderDate) AS NewDate FROM Sales.Orders WHERE OrderID = 1001

 

注意，SELECT列表使用了DATEADD方法。這個方法有三個參數：mm指月，3指月數，而OrderDate是一個DATETIME值。因此，當查詢傳回值時，每個OrderDate值都會增加三個月時間，如下的結果所示：

 

OrderDate	NewDate
2008-08-27 13:36:16.280	2008-11-27 13:36:16.280

 

如上所示，日期August 27已經被改為November 27。而且，這樣的運算還不僅限於日期。下面我在OrderDate值中增加三個小時：

 

SELECT OrderDate， DATEADD(hh， 3， OrderDate) AS NewTime FROM Sales.Orders WHERE OrderID = 1001

 

DATEADD的第一個參數現在是hh，而不是mm，因此，只有小時被改變了，如下結果所示：

 

OrderDate	NewTime
2008-08-27 13:36:16.280	2008-08-27 16:36:16.280

 

日期/時間值也可以減去一定的日期或時間間隔。在下例中，我從OrderDate值中減去了三天：

 

SELECT OrderDate， DATEADD(dd， -3， OrderDate) AS PastDate FROM Sales.Orders WHERE OrderID = 1001

 

注意，DATEADD的第一個參數現在是dd。同時，注意，第二個參數是一個負數，這意味著將有三天被減去，如下所示：

 

OrderDate	PastDate
2008-08-27 13:36:16.280	2008-08-24 13:36:16.280

 

這樣，新的日期是August 24而不是August 27。

 

這樣，上面的例子示範如何在從資料庫查詢到日期/時間值後再對它進行修改。而DATEADD方法同樣也可以用來插入日期/時間資料。因為DATEADD方法返回一個DATETIME值。（如果所提供的日期對應的方法是SMALLDATETIME，那麼它將返回一個SMALLDATETIME值。）在下面的例子中，我添加了一行資料到Sales.Orders表中，然後使用SELECT語句來檢索這個行：

 

INSERT INTO Sales.Orders VALUES(1002， GETDATE()， DATEADD(dd， 10， GETDATE())) GO SELECT * FROM Sales.Orders WHERE OrderID = 1002

 

注意，VALUES子句包含了表中每個欄位的值。對於OrderDate值，我使用GETDATE()方法來擷取當前的日期和時間。對於DelivDate欄位，我使用DATEADD方法以及相應的三個參數。第一個參數dd表示將要添加到日期中的是天數。第二個參數10意味著將添加10天到日期中。最後，第三個參數是GETDATE方法。因此，10天將添加到目前的日期和時間中並插入到DelivDate欄位。這就是SELECT語句產生的結果：

 

OrderID	OrderDate	DelivDate
1002	2008-08-27 13:40:22.357	2008-09-06 13:40:22.357

 

正如所期待的，DelivDate值比OrderDate晚10天。

 

現在讓我們來檢測一個使用了DATEADD方法的UPDATE語句。在下面的語句中，我從DelivDate值中減去了三天，然後顯示了結果：

 

UPDATE Sales.Orders SET DelivDate = DATEADD(dd， -3， DelivDate) WHERE OrderID = 1002 GO SELECT * FROM Sales.Orders WHERE OrderID = 1002

 

這次我在SET子句中使用了DATEADD——我將DelivDate值設為DATEADD方法返回的結果。這個方法指定天數（dd）為第一個參數，-3為第二個參數，而DelivDate欄位為第三個參數。這就意味著該方法將返回一個比原始日期早三天的日期，並將DelivDate設定為新的日期，如下結果顯示：

 

OrderID	OrderDate	DelivDate
1002	2008-08-27 13:40:22.357	2008-09-03 13:40:22.357

 

你應該記得，INSERT語句（在前一個例子）添加了一個DelivDate值為September 6的行。但是，這個值現在是September 3，比原來早了三天。

使用DATEDIFF方法

 

DATEDIFF方法可以計算兩個日期之間的時間間隔，並返回一個代表間隔的整數。這個方法使用下面的文法：

 

DATEDIFF(<date/time_part>， <start_date>， <end_date>)

 

<date/time_part>預留位置指的是兩個日期中需要比較的部分。比如，你想確認開始日期和結束日期之間的小時數或天數。

 

除了工作日(dw， w)縮寫之外，<date/time_part>預留位置使用的縮寫與DATEADD方法一樣。DATEDIFF不支援工作日比較。

 

<start_date>預留位置指的是比較的開始日期，而<end_date>預留位置指的是結束日期。換言之，方法將返回開始日期和結束日期之間的具體時間或日期間隔。

 

讓我們舉例來說明它是如何工作的。下面的SELECT語句計算了Sales.Orders 表中OrderDate和DelivDate值之間的時間間隔：

 

SELECT OrderDate， DelivDate，  DATEDIFF(dd， OrderDate， DelivDate) AS DaysDiff FROM Sales.Orders WHERE OrderID = 1002

 

在這個語句中，我在SELECT列表中使用了DATEDIFF。這個方法的第一參數指定間隔必須是天數（dd），而第二個參數指定OrderDate作為開始日期，然後第三個參數指定DelivDate作為結束日期。因此，DATEDIFF將計算OrderDate和DelivDate之間的天數，在此例子中，它是7天，如下結果所示：

 

OrderDate	DelivDate	DaysDiff
2008-08-27 13:40:22.357	2008-09-03 13:40:22.357	7

 

當然，它也可以用來計算各種時間間隔，如下例的語句所示：

 

SELECT OrderDate， DelivDate，  DATEDIFF(hh， OrderDate， DelivDate) AS HoursDiff FROM Sales.Orders WHERE OrderID = 1002

 

在這種情況下，方法的第一個參數是小時（hh）而非天數。因此，方法將返回OrderDate 和DelivDate值之間相差的小時數，如下結果所示：

 

OrderDate	DelivDate	HoursDiff
2008-08-27 13:40:22.357	2008-09-03 13:40:22.357	168

 

兩個值之間相差168個小時。

 

與DATEADD方法一樣，DATEDIFF方法並不僅限於用在SELECT語句中。比如，DATEDIFF可以用在UPDATE語句的WHERE子句中，以確定哪一行需要更新。在下例中，我使用了DATEDIFF來指定這些在OrderDate和DelivDate值之間天數少於8的行。

 

UPDATE Sales.Orders SET DelivDate = DATEADD(dd， 3， DelivDate) WHERE DATEDIFF(dd， OrderDate， DelivDate) < 8 GO SELECT OrderID， OrderDate， DelivDate，  DATEDIFF(dd， OrderDate， DelivDate) AS DaysDiff FROM Sales.Orders

 

在前面的例子中，DATEDIFF方法返回了OrderDate和DelivDate值之間的天數。然後這個數目將與8作比較。如果天數少於8，那麼這一行將被更新；否則，該行將不改變。對於這些需要更新的行，我使用DATEADD方法來增加三天到DelivDate值中。然後，我運行一個SELECT語句來返回Sales.Orders表的資料以及計算每個行中兩個日期的不同，如下結果所示：

 

OrderID	OrderDate	DelivDate	DaysDiff
1001	2008-08-27 13:36:16.280	2008-09-08 18:27:10.750	12
1002	2008-08-27 13:40:22.357	2008-09-06 13:40:22.357	10

 

結果顯示現在在兩個日期（第二行中）之間是相差10天，而非原來的7天。

 

在表定義中使用DATEADD和DATEDIFF

 

DATEADD和DATEDIFF方法也可以用在表定義中。例如，欄位定義的DEFAULT子句中可以用DATEADD方法或使用DATEDIFF方法來建立一個計算得來的欄位。在下面的Transact-SQL代碼中，我首先建立了使用DATEADD和DATEDIFF的表，然後添加一行資料到表中，最後檢索表的資料：

 

USE AdventureWorks GO IF EXISTS (SELECT table_name  FROM information_schema.tables  WHERE table_schema = 'Sales' AND table_name = 'Orders') DROP TABLE Sales.Orders GO CREATE TABLE Sales.Orders ( OrderID INT NOT NULL， OrderDate DATETIME NOT NULL DEFAULT GETDATE()， DelivDate DATETIME NOT NULL DEFAULT DATEADD(dd， 10， GETDATE())， DaysDiff AS DATEDIFF(dd， OrderDate， DelivDate) )  GO INSERT INTO Sales.Orders(OrderID) VALUES(1001) GO SELECT OrderID， OrderDate， DelivDate， DaysDiff FROM Sales.Orders

 

在CREATE TABLE語句中，我建立了四個欄位，其中三個儲存日期/時間資料。OrderDate欄位直接使用GETDATE來產生預設值。DelivDate欄位也有一個預設值。然而，這個預設值是基於DATEADD返回的結果的，同時，在這種情況下，我使用方法增加10天到GETDATE返回的值儲存到DelivDate欄位中。最後，DaysDiff欄位是一個計算得來的欄位，它的值是使用DATADIFF來計算OrderDate和DelivDate值之間的天數差。

 

在表定義之後，我插入一資料行到表中。因為所有的日期/時間值都是自動產生的，因此我僅僅需要插入OrderID值，如下所示：

 

OrderID	OrderDate	DelivDate	DaysDiff
1001	2008-08-27 13:42:50.433	2008-09-06 13:42:50.433	10

 

DATEADD和DATEDIFF方法不僅僅在表定義中非常有用，同樣也適用於查詢和資料修改語句。通過DATEADD，我們可以將日期/時間值增加和減少一定值，而通過DATEDIFF，我們可以計算日期/時間值之間的時間間隔。更多詳細的關於這些方法的資訊，可以閱讀Microsoft SQL Server Books Online。