Share via

  • 發行項

DATEADD (Transact-SQL)

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

如需所有 Transact-SQL 日期和時間資料類型與函數的概觀，請參閱＜日期和時間函數 (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)，或者包含時區位移部分，就會引發錯誤。

[!附註]

當 DATEADD 搭配其他日期/時間函數使用時，如果字串常值沒有針對 date 參數明確轉換，則使用日月年 (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 引數不得超過 int 的範圍。在下列陳述式中，number 引數超過 int 的範圍 (超過 1)。傳回下列錯誤訊息：「轉換運算式到資料類型 int 時發生算術溢位錯誤。」

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

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

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

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 AdventureWorks2008R2;
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 BusinessEntityID FROM Person.Person) 是數字參數的假造引數，可示範如何從值清單中選取 number 引數。

USE AdventureWorks2008R2;
GO
SELECT DATEADD(month,(SELECT TOP 1 BusinessEntityID FROM Person.Person),
    (SELECT MAX(ModifiedDate) FROM Person.Person));

指定常數成為 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 AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName
    ,DATEADD(day,ROW_NUMBER() OVER (ORDER BY
        a.PostalCode),SYSDATETIME()) AS 'Row Number'
FROM Sales.SalesPerson AS s 
    INNER JOIN Person.Person AS p 
        ON s.BusinessEntityID = p.BusinessEntityID
    INNER JOIN Person.Address AS a 
        ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

指定彙總視窗函數成為 number

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

USE AdventureWorks2008R2;
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;

GO

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

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

GO

第一個 SELECT 陳述式會傳回 julio (7 月) 代表月份，而第二個 SELECT 陳述式則傳回 marzo (3 月) 代表月份。

透過明確轉換字串常值，避免錯誤的結果

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

SET LANGUAGE Español;

GO

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

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

GO

這兩個 SELECT 陳述式都會傳回 marzo (3 月) 代表月份。

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

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

SET LANGUAGE Español;

GO

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

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

SELECT DATENAME(m, @d);

GO