發行項
09/24/2010

DATEADD (Transact-SQL)

傳回指定的 date，並將指定的 number 間隔 (帶正負號的整數) 加入至該 date 的指定 datepart。

如需所有 Transact-SQL 日期和時間資料類型與函數的概觀，請參閱＜日期和時間資料類型與函數 (Transact-SQL)＞。如需日期和時間資料類型與函數常用的資訊和範例，請參閱＜使用日期和時間資料＞。

語法

DATEADD (datepart , number, date )

引數

datepart
這是 integernumber 要加入其中的 date 部分。下表列出所有有效的 datepart 引數。使用者定義變數對等項目無效。

datepart	縮寫
year	yy,yyyy
quarter	qq,q
month	mm,m
dayofyear	dy,y
day	dd,d
week	wk,ww
weekday	dw,w
hour	hh
minute	mi,n
second	ss,s
millisecond	ms
microsecond	mcs
nanosecond	ns

number
這是可解析成 int (要加入至 date 的 datepart) 的運算式。使用者自訂的變數有效。

如果您指定了含有十進位小數的值，該小數就會被截斷而且不會四捨五入。
date
這是可解析成 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值的運算式。date 可以是運算式、資料行運算式、使用者自訂變數或字串常值。如果此運算式為字串常值，它必須解析為 datetime。若要避免模糊不清，請使用四位數年份。如需兩位數年份的詳細資訊，請參閱＜two digit year cutoff 選項＞。

傳回類型

傳回資料類型是 date 引數的資料類型，但字串常值除外。

字串常值的傳回資料類型是 datetime。如果字串常值的秒數小數位數超過三個位數 (.nnn)，或者包含時差部分，就會引發錯誤。

[!附註]

如果未明確針對 date 參數轉換字串常值，則當 DATEADD 搭配其他日期/時間函數使用時，使用「日-月-年」(dmy) 日期格式的區域變數可能會得到不正確的結果。

傳回 datetime2 類型

當 date 參數為 datetime2 類型時，DATEADD 會傳回 datetime2 類型。針對 date 參數使用字串常值時，您必須明確將其轉換為 datetime2 類型，DATEADD 才會傳回 datetime2 類型。

傳回值

datepart 引數

dayofyear、day 和 weekday 都會傳回相同的值。

每個 datepart 及其縮寫都會傳回相同的值。

如果 datepart 是 month、date 月份的天數比傳回月份的天數多，而且 date 日期不存在傳回月份中，就會傳回傳回月份的最後一天。例如，九月有 30 天。因此，下列陳述式會傳回 2006-09-30 00:00:00.000：

SELECT DATEADD(month, 1, '2006-08-30')

SELECT DATEADD(month, 1, '2006-08-31')

number 引數

number 引數不得超過 int 的範圍。在下列陳述式中，number 引數超過 int 的範圍 (超過 1)。傳回下列錯誤訊息：「轉換運算式到資料類型 int 時發生算術溢位錯誤。」

SELECT DATEADD(year,2147483648, '2006-07-31');
SELECT DATEADD(year,-2147483649, '2006-07-31');

date 引數

date 引數不得遞增為超過其資料類型之範圍的值。在下列陳述式中，加入至 date 值的 number 值超過 date 資料類型的範圍。如此就會傳回下列錯誤訊息：「新增一個值到 'datetime' 資料行時造成溢位」。

SELECT DATEADD(year,2147483647, '2006-07-31');
SELECT DATEADD(year,-2147483647, '2006-07-31');

smalldatetime date 和 second 或小數秒數 datepart 的傳回值

smalldatetime 值的秒數部分一律為 00。如果 date 是 smalldatetime，就會適用下列情況：

如果 datepart 是 second 而且 number 介於 -30 與 +29 之間，就不會執行任何加法。
如果 datepart 是 second 而且 number 小於 -30 或大於 +29，就會從一分鐘開始執行加法。
如果 datepart 是 millisecond 而且 number 介於 -30001 與 +29998 之間，就不會執行任何加法。
如果 datepart 是 millisecond 而且 number 小於 -30001 或大於 +29998，就會從一分鐘開始執行加法。

備註

DATEADD 可用於 SELECT <list>、WHERE、HAVING、GROUP BY 和 ORDER BY 子句中。

小數秒數有效位數

不允許針對 date 資料類型 smalldatetime、date 和 datetime 的 microsecond 或 nanoseconddatepart 執行加法。

毫秒具有小數位數 3 (.123)。微秒具有小數位數 6 (.123456)。奈秒具有小數位數 9 (.123456789)。time、datetime2 和 datetimeoffset 資料類型都具有最大小數位數 7 (.1234567)。如果 datepart 是 nanosecond，在 date 的小數秒數增加之前，number 必須是 100。介於 1 與 49 之間的 number 會向下捨入到 0，而 50 至 99 之間的數字則會向上捨入到 100。

下列陳述式會加入 millisecond、microsecond 或 nanosecond 的 datepart。

DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111'
SELECT '1 millisecond' ,DATEADD(millisecond,1,@datetime2)
UNION ALL
SELECT '2 milliseconds', DATEADD(millisecond,2,@datetime2)
UNION ALL
SELECT '1 microsecond', DATEADD(microsecond,1,@datetime2)
UNION ALL
SELECT '2 microseconds', DATEADD(microsecond,2,@datetime2)
UNION ALL
SELECT '49 nanoseconds', DATEADD(nanosecond,49,@datetime2)
UNION ALL
SELECT '50 nanoseconds', DATEADD(nanosecond,50,@datetime2)
UNION ALL
SELECT '150 nanoseconds', DATEADD(nanosecond,150,@datetime2);
/*
Returns:
1 millisecond     2007-01-01 13:10:10.1121111
2 milliseconds    2007-01-01 13:10:10.1131111
1 microsecond     2007-01-01 13:10:10.1111121
2 microseconds    2007-01-01 13:10:10.1111131
49 nanoseconds    2007-01-01 13:10:10.1111111
50 nanoseconds    2007-01-01 13:10:10.1111112
150 nanoseconds   2007-01-01 13:10:10.1111113
*/

時區位移

不允許針對時區位移執行加法。

範例

A. 以 1 為間隔遞增 datepart

下列每個陳述式都會以 1 為間隔遞增 datepart。

DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111'
SELECT 'year', DATEADD(year,1,@datetime2)
UNION ALL
SELECT 'quarter',DATEADD(quarter,1,@datetime2)
UNION ALL
SELECT 'month',DATEADD(month,1,@datetime2)
UNION ALL
SELECT 'dayofyear',DATEADD(dayofyear,1,@datetime2)
UNION ALL
SELECT 'day',DATEADD(day,1,@datetime2)
UNION ALL
SELECT 'week',DATEADD(week,1,@datetime2)
UNION ALL
SELECT 'weekday',DATEADD(weekday,1,@datetime2)
UNION ALL
SELECT 'hour',DATEADD(hour,1,@datetime2)
UNION ALL
SELECT 'minute',DATEADD(minute,1,@datetime2)
UNION ALL
SELECT 'second',DATEADD(second,1,@datetime2)
UNION ALL
SELECT 'millisecond',DATEADD(millisecond,1,@datetime2)
UNION ALL
SELECT 'microsecond',DATEADD(microsecond,1,@datetime2)
UNION ALL
SELECT 'nanosecond',DATEADD(nanosecond,1,@datetime2);
/*
Year         2008-01-01 13:10:10.1111111
quarter      2007-04-01 13:10:10.1111111
month        2007-02-01 13:10:10.1111111
dayofyear    2007-01-02 13:10:10.1111111
day          2007-01-02 13:10:10.1111111
week         2007-01-08 13:10:10.1111111
weekday      2007-01-02 13:10:10.1111111
hour         2007-01-01 14:10:10.1111111
minute       2007-01-01 13:11:10.1111111
second       2007-01-01 13:10:11.1111111
millisecond  2007-01-01 13:10:10.1121111
microsecond  2007-01-01 13:10:10.1111121
nanosecond   2007-01-01 13:10:10.1111111
*/

B. 在單一陳述式中遞增一個以上的 datepart 層級

下列每個陳述式都會利用足以同時遞增 date 中下一個較高 datepart 的 number，遞增 datepart。

DECLARE @datetime2 datetime2;
SET @datetime2 = '2007-01-01 01:01:01.1111111';
--Statement                                 Result   
-------------------------------------------------------------------                                   
SELECT DATEADD(quarter,4,@datetime2);     --2008-01-01 01:01:01.110
SELECT DATEADD(month,13,@datetime2);      --2008-02-01 01:01:01.110
SELECT DATEADD(dayofyear,365,@datetime2); --2008-01-01 01:01:01.110
SELECT DATEADD(day,365,@datetime2);       --2008-01-01 01:01:01.110
SELECT DATEADD(week,5,@datetime2);        --2007-02-05 01:01:01.110
SELECT DATEADD(weekday,31,@datetime2);    --2007-02-01 01:01:01.110
SELECT DATEADD(hour,23,@datetime2);       --2007-01-02 00:01:01.110
SELECT DATEADD(minute,59,@datetime2);     --2007-01-01 02:00:01.110
SELECT DATEADD(second,59,@datetime2);     --2007-01-01 01:02:00.110
SELECT DATEADD(millisecond,1,@datetime2); --2007-01-01 01:01:01.110

C. 使用運算式當做 number 和 date 參數的引數

下列範例會使用不同的運算式類型，當做 number 和 date 參數的引數。

指定資料行成為 date

下列範例會將 2 天加入至每個 OrderDate，以便計算新的 PromisedShipDate。

USE AdventureWorks;
GO
SELECT SalesOrderID
    ,OrderDate 
    ,DATEADD(day,2,OrderDate) AS PromisedShipDate
FROM Sales.SalesOrderHeader;

指定使用者自訂變數成為 number 和 date

下列範例會指定使用者自訂變數成為 number 和 date 的引數。

DECLARE @days int;
DECLARE @datetime datetime;
SET @days = 365;
SET @datetime = '2000-01-01 01:01:01.111'; /* 2000 was a leap year */
SELECT DATEADD(day, @days, @datetime);

指定純量系統函數成為 date

下列範例會指定 date 的 SYSDATETIME。

SELECT DATEADD(month, 1, SYSDATETIME());

指定純量子查詢和純量函數成為 number 和 date

下列範例使用純量子查詢和純量函數MAX(ModifiedDate) 當做 number 和 date 的引數。(SELECT TOP 1 ContactID FROM Person.Contact) 是數字參數的假造引數，可示範如何從值清單中選取 number 引數。

USE AdventureWorks;
GO
SELECT DATEADD(month,(SELECT TOP 1 ContactID FROM Person.Contact),
    (SELECT MAX(ModifiedDate) FROM Person.Contact));

指定常數成為 number 和 date

下列範例會使用數值和字元常數，當做 number 和 date 的引數。

SELECT DATEADD(minute, 1, '2007-05-07 09:53:01.0376635');

指定數值運算式和純量系統函數成為 number 和 date

下列範例會使用數值運算式 (-(10/2))、一元運算子 (-)、算術運算子 (/) 和純量系統函數 (SYSDATETIME)，當做 number 和 date 的引數。

SELECT DATEADD(month,-(10/2), SYSDATETIME());

指定排名函數成為 number

下列範例會使用排名函數，當做 number 的引數。

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,DATEADD(day,ROW_NUMBER() OVER (ORDER BY
        a.PostalCode),SYSDATETIME()) AS 'Row Number'
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

指定彙總視窗函數成為 number

下列範例會使用彙總視窗函數，當做 number 的引數。

USE AdventureWorks;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,DATEADD(day,SUM(OrderQty) 
        OVER(PARTITION BY SalesOrderID),SYSDATETIME()) AS 'Total'
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);
GO

D. 針對使用 dmy 日期格式的區域變數使用 DATEADD

下列範例示範如何針對某些區域變數來搭配 DATEADD 使用字串常值。

示範使用字串常值之隱含轉換的錯誤

下列範例會示範未明確轉換字串常值時所發生的事情。

SET LANGUAGE Español;

SELECT DATENAME(m, DATEADD(d, 0,'1987-03-07'));

SELECT DATENAME(m, '1987-03-07');

第一個 select 陳述式會在月份中傳回 julio (七月)，第二個 select 陳述式會在月份中傳回 marzo (三月)。

明確轉換字串常值來避免錯誤結果

下列範例示範如何明確轉換 date 參數來避免錯誤結果。

SET LANGUAGE Español;

SELECT DATENAME(m, DATEADD(d, 0, CAST('1987-03-07' AS datetime2)));

SELECT DATENAME(m, '1987-03-07');

兩個 select 陳述式都會在月份中傳回 marzo (三月)。

使用 datetime2 變數來取代字串常值

下列範例會避免直接使用字串常值。

SET LANGUAGE Español;

DECLARE @d datetime2 = '1987-03-07';

SELECT DATENAME(m, DATEADD(d, 0, @d));

SELECT DATENAME(m, @d);

請參閱

參考

CAST 和 CONVERT (Transact-SQL)

DATEADD (Transact-SQL)

語法

引數

傳回類型

傳回 datetime2 類型

傳回值

datepart 引數

number 引數

date 引數

smalldatetime date 和 second 或小數秒數 datepart 的傳回值

備註

小數秒數有效位數

時區位移

範例

A. 以 1 為間隔遞增 datepart

B. 在單一陳述式中遞增一個以上的 datepart 層級

C. 使用運算式當做 number 和 date 參數的引數

指定資料行成為 date

指定使用者自訂變數成為 number 和 date

指定純量系統函數成為 date

指定純量子查詢和純量函數成為 number 和 date

指定常數成為 number 和 date

指定數值運算式和純量系統函數成為 number 和 date

指定排名函數成為 number

指定彙總視窗函數成為 number

D. 針對使用 dmy 日期格式的區域變數使用 DATEADD

示範使用字串常值之隱含轉換的錯誤

明確轉換字串常值來避免錯誤結果

使用 datetime2 變數來取代字串常值

請參閱

參考

其他資源