DATEADD (Transact-SQL)DATEADD (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

此函式會將指定的 number 值(以帶正負號的整數形式) 加到輸入 date 值的指定 datepart,然後傳回該修改過的值。This function adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value.

如需所有 Transact-SQLTransact-SQL 日期和時間資料類型與函式的概觀,請參閱日期和時間資料類型與函式 (Transact-SQL)See Date and Time Data Types and Functions (Transact-SQL) for an overview of all Transact-SQLTransact-SQL date and time data types and functions.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

DATEADD (datepart , number , date )  

引數Arguments

datepartdatepart
dateDATEADD 要加上 整數 number 的部分。The part of date to which DATEADD adds an integer number. 此表格會列出所有有效的 datepart 引數。This table lists all valid datepart arguments.

注意

DATEADD 不會接受 datepart 引數的使用者定義變數對等項目。DATEADD does not accept user-defined variable equivalents for the datepart arguments.

datepartdatepart 縮寫Abbreviations
yearyear yy, yyyyyy, yyyy
quarterquarter qq, qqq, q
monthmonth mm, mmm, m
dayofyeardayofyear dy, ydy, y
dayday dd, ddd, d
weekweek wk, wwwk, ww
weekdayweekday dw, wdw, w
hourhour hhhh
minuteminute mi, nmi, n
secondsecond ss, sss, s
millisecondmillisecond msms
microsecondmicrosecond mcsmcs
nanosecondnanosecond nsns

numbernumber
可解析成 int (DATEADD 要加到 datedatepart) 的運算式。An expression that can resolve to an int that DATEADD adds to a datepart of date. DATEADD 接受 number 的使用者定義變數值。DATEADD accepts user-defined variable values for number. DATEADD 將會截斷具有十進位小數的指定 number 值。DATEADD will truncate a specified number value that has a decimal fraction. 在此情況下,不會捨入 number 值。It will not round the number value in this situation.

datedate
可解析成下列其中一個值的運算式:An expression that can resolve to one of the following values:

  • datedate
  • datetimedatetime
  • datetimeoffsetdatetimeoffset
  • datetime2datetime2
  • smalldatetimesmalldatetime
  • timetime

針對 dateDATEADD 會接受資料行運算式、運算式、字串常值或使用者定義變數。For date, DATEADD will accept a column expression, expression, string literal, or user-defined variable. 字串常值必須解析成 datetimeA string literal value must resolve to a datetime. 請使用四位數年份以避免模糊不清的問題。Use four-digit years to avoid ambiguity issues. 如需兩位數年份的資訊,請參閱設定兩位數年份的截止伺服器設定選項See Configure the two digit year cutoff Server Configuration Option for information about two-digit years.

傳回類型Return types

這個方法的傳回值資料類型為動態。The return value data type for this method is dynamic. 傳回型別取決於提供給 date 的引數而定。The return type depends on the argument supplied for date. 如果 date 的值是字串常值日期,則 DATEADD 會傳回 datetime 值。If the value for date is a string literal date, DATEADD returns a datetime value. 如果提供其他有效輸入資料類型給 date,則 DATEADD 會傳回相同的資料類型。If another valid input data type is supplied for date, DATEADD returns the same data type. 如果字串常值的秒數小數位數超過三個小數位數位置 (.nnn),者字串常值包含時區時差部分,則 DATEADD 會引發錯誤。DATEADD raises an error if the string literal seconds scale exceeds three decimal place positions (.nnn) or if the string literal contains the time zone offset part.

傳回值Return Value

datepart 引數datepart Argument

dayofyeardayweekday 都會傳回相同的值。dayofyear, day, and weekday return the same value.

每個 datepart 及其縮寫都會傳回相同的值。Each datepart and its abbreviations return the same value.

如果下列條件成立:If the following are true:

  • datepart月份datepart is month
  • date 月的天數多於傳回月份the date month has more days than the return month
  • date 日不存在於傳回月份the date day does not exist in the return month

DATEADD 則會傳回該傳回月份的最後一天。Then, DATEADD returns the last day of the return month. 例如,九月有 30 (三十) 天。因此,這些陳述式會傳回 2006-09-30 00:00:00.000:For example, September has 30 (thirty) days; therefore, these statements return 2006-09-30 00:00:00.000:

SELECT DATEADD(month, 1, '20060830');
SELECT DATEADD(month, 1, '20060831');

number 引數number Argument

number 引數不得超過 int 的範圍。在下列陳述式中,number 引數超過 int 的範圍 (超過 1)。The number argument cannot exceed the range of int. In the following statements, the argument for number exceeds the range of int by 1. 這些陳述式都會傳回下列錯誤訊息:"Msg 8115, Level 16, State 2, Line 1. Arithmetic overflow error converting expression to data type int."These statements both return the following error message: "Msg 8115, Level 16, State 2, Line 1. Arithmetic overflow error converting expression to data type int."

SELECT DATEADD(year,2147483648, '20060731');  
SELECT DATEADD(year,-2147483649, '20060731');  

date 引數date Argument

DATEADD 將不會接受遞增至超過其資料類型範圍值的 dateDATEADD will not accept a date argument incremented to a value outside the range of its data type. 在下列陳述式中,加到 date 值的 number 值超過 date 資料類型的範圍。In the following statements, the number value added to the date value exceeds the range of the date data type. DATEADD 會傳回下列錯誤訊息:"Msg 517, Level 16, State 1, Line 1 Adding a value to a 'datetime' column caused overflow"。DATEADD returns the following error message: "Msg 517, Level 16, State 1, Line 1 Adding a value to a 'datetime' column caused overflow."

SELECT DATEADD(year,2147483647, '20060731');  
SELECT DATEADD(year,-2147483647, '20060731');  

smalldatetime date 和 second 或小數秒數 datepart 的傳回值Return Values for a smalldatetime date and a second or Fractional Seconds datepart

smalldatetime 值的秒數部分一律為 00。The seconds part of a smalldatetime value is always 00. 對於 smalldatetime date 值,適用下列情況:For a smalldatetime date value, the following apply:

  • 如果是 seconddatepart,且 number 介於 -30 到 +29 之間,DATEADD 不會變更。For a datepart of second, and a number value between -30 and +29, DATEADD makes no changes.
  • 如果是 seconddatepart,且 number 值小於 -30 或大於 +29,DATEADD 會從一分鐘開始執行加法。For a datepart of second, and a number value less than -30, or more than +29, DATEADD performs its addition beginning at one minute.
  • 如果是 milliseconddatepart,且 number 介於 -30001 到 +29998 之間,DATEADD 不會變更。For a datepart of millisecond and a number value between -30001 and +29998, DATEADD makes no changes.
  • 如果是 milliseconddatepart,且 number 值小於 -30001 或大於 +29998,DATEADD 會從一分鐘開始執行加法。For a datepart of millisecond and a number value less than -30001, or more than +29998, DATEADD performs its addition beginning at one minute.

RemarksRemarks

在下列子句中使用 DATEADDUse DATEADD in the following clauses:

  • GROUP BYGROUP BY
  • HAVINGHAVING
  • ORDER BYORDER BY
  • SELECT <list>SELECT <list>
  • WHEREWHERE

小數秒數有效位數Fractional seconds precision

DATEADD不允許針對 date 資料類型 smalldatetimedatedatetime 執行 microsecondnanoseconddatepart 加法。DATEADD does not allow addition for a datepart of microsecond or nanosecond for date data types smalldatetime, date, and datetime.

毫秒具有小數位數 3 (.123),微秒具有小數位數 6 (.123456),奈秒具有小數位數 9 (.123456789)。Milliseconds have a scale of 3 (.123), microseconds have a scale of 6 (.123456), and nanoseconds have a scale of 9 (.123456789). timedatetime2datetimeoffset 資料類型都具有最大小數位數 7 (.1234567)。The time, datetime2, and datetimeoffset data types have a maximum scale of 7 (.1234567). 如果是 nanoseconddatepart,在 date 的小數秒數增加之前,number 必須是 100。For a datepart of nanosecond, number must be 100 before the fractional seconds of date increase. 1 與 49 之間的 number 會無條件捨去成 0,而 50 到 99 的 number 會無條件進位到 100。A number between 1 and 49 will round down to 0, and a number from 50 to 99 rounds up to 100.

這些陳述式會加上 millisecondmicrosecondnanoseconddatepartThese statements add a datepart of millisecond, microsecond, or nanosecond.

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);  

以下為結果集:Here is the result set.

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  

時區位移Time zone offset

DATEADD 不允許針對時區時差執行加法。DATEADD does not allow addition for time zone offset.

範例Examples

A.A. 以 1 為間隔遞增 datepartIncrementing datepart by an interval of 1

以下每個陳述式都會以 1 為間隔遞增 datepartEach of these statements increments datepart by an interval of 1:

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);  

以下為結果集:Here is the result set.

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.B. 在單一陳述式中遞增一個以上的 datepart 層級Incrementing more than one level of datepart in one statement

以下每個陳述式都會利用足以同時遞增 date 中下一個較高 datepartnumber來遞增 datepartEach of these statements increments datepart by a number large enough to additionally increment the next higher datepart of date:

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.1111111  
SELECT DATEADD(month,13,@datetime2);      --2008-02-01 01:01:01.1111111  
SELECT DATEADD(dayofyear,365,@datetime2); --2008-01-01 01:01:01.1111111  
SELECT DATEADD(day,365,@datetime2);       --2008-01-01 01:01:01.1111111  
SELECT DATEADD(week,5,@datetime2);        --2007-02-05 01:01:01.1111111  
SELECT DATEADD(weekday,31,@datetime2);    --2007-02-01 01:01:01.1111111  
SELECT DATEADD(hour,23,@datetime2);       --2007-01-02 00:01:01.1111111  
SELECT DATEADD(minute,59,@datetime2);     --2007-01-01 02:00:01.1111111  
SELECT DATEADD(second,59,@datetime2);     --2007-01-01 01:02:00.1111111  
SELECT DATEADD(millisecond,1,@datetime2); --2007-01-01 01:01:01.1121111  

C.C. 使用運算式當做 number 和 date 參數的引數Using expressions as arguments for the number and date parameters

這些範例會使用不同的運算式類型,作為 numberdate 參數的引數。These examples use different types of expressions as arguments for the number and date parameters. 範例使用的是 AdventureWorks 資料庫。The examples use the AdventureWorks database.

指定資料行成為 dateSpecifying a column as date

此範例會將 2 兩天加到 OrderDate 資料行中的每個值,以便衍生名為 PromisedShipDate 的新資料行:This example adds 2 (two) days to each value in the OrderDate column, to derive a new column named PromisedShipDate:

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

部分結果集:A partial result set:

SalesOrderID OrderDate               PromisedShipDate  
------------ ----------------------- -----------------------  
43659        2005-07-01 00:00:00.000 2005-07-03 00:00:00.000  
43660        2005-07-01 00:00:00.000 2005-07-03 00:00:00.000  
43661        2005-07-01 00:00:00.000 2005-07-03 00:00:00.000  
...  
43702        2005-07-02 00:00:00.000 2005-07-04 00:00:00.000  
43703        2005-07-02 00:00:00.000 2005-07-04 00:00:00.000  
43704        2005-07-02 00:00:00.000 2005-07-04 00:00:00.000  
43705        2005-07-02 00:00:00.000 2005-07-04 00:00:00.000  
43706        2005-07-03 00:00:00.000 2005-07-05 00:00:00.000  
...  
43711        2005-07-04 00:00:00.000 2005-07-06 00:00:00.000  
43712        2005-07-04 00:00:00.000 2005-07-06 00:00:00.000  
...  
43740        2005-07-11 00:00:00.000 2005-07-13 00:00:00.000  
43741        2005-07-12 00:00:00.000 2005-07-14 00:00:00.000  
  

指定使用者自訂變數成為 number 和 dateSpecifying user-defined variables as number and date

此範例會將使用者定義變數指定為 numberdate 的引數:This example specifies user-defined variables as arguments for number and date:

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

以下為結果集:Here is the result set.

-----------------------  
2000-12-31 01:01:01.110  
  
(1 row(s) affected)  

指定純量系統函數成為 dateSpecifying scalar system function as date

這個範例會針對 date 指定 SYSDATETIMEThis example specifies SYSDATETIME for date. 傳回的精確值取決於陳述式執行的日期和時間:The exact value returned depends on the day and time of statement execution:

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

以下為結果集:Here is the result set.

---------------------------  
2013-02-06 14:29:59.6727944  
  
(1 row(s) affected)  

指定純量子查詢和純量函數成為 number 和 dateSpecifying scalar subqueries and scalar functions as number and date

此範例會使用純量子查詢 MAX(ModifiedDate),作為 numberdate 的引數。This example uses scalar subqueries, MAX(ModifiedDate), as arguments for number and date. (SELECT TOP 1 BusinessEntityID FROM Person.Person) 會作為 number 參數的人工引數,以示範如何從值清單中選取 number 引數。(SELECT TOP 1 BusinessEntityID FROM Person.Person) serves as an artificial argument for the number parameter, to show how to select a number argument from a value list.

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

指定數值運算式和純量系統函數成為 number 和 dateSpecifying numeric expressions and scalar system functions as number and date

此範例會使用數值運算式 (-(10/2))一元運算子 (-)、算數運算子 (/) 和純量系統函式 (SYSDATETIME),作為 numberdate 的引數。This example uses a numeric expression (-(10/2)), unary operators (-), an arithmetic operator (/), and scalar system functions (SYSDATETIME) as arguments for number and date.

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

指定排名函數成為 numberSpecifying ranking functions as number

此範例會使用次序函數,作為 number 的引數。This example uses a ranking function as an argument for number.

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;  

指定彙總視窗函數成為 numberSpecifying an aggregate window function as number

此範例會使用彙總視窗函式,作為 number 的引數。This example uses an aggregate window function as an argument for number.

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

另請參閱See also

CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)