DATEADD (Transact-SQL)

将指定 number 时间间隔(有符号整数)与指定 date 的指定 datepart 相加后,返回该 date。

有关所有 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
    是一个表达式,可以解析为与 date 的 datepart 相加的 int。用户定义的变量是有效的。

    如果您指定一个带小数的值,则将小数截去且不进行舍入。

  • date
    是一个表达式,可以解析为 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值。date 可以是表达式、列表达式、用户定义的变量或字符串文字。如果表达式是字符串文字,则它必须解析为一个 datetime 值。为避免不确定性,请使用四位数年份。有关两位数年份的信息,请参阅 two digit year cutoff 选项

返回类型

返回数据类型为 date 参数的数据类型,字符串文字除外。

字符串文字的返回数据类型为 datetime。如果字符串文字的秒数小数位数超过三位 (.nnn) 或包含时区偏移量部分,将引发错误。

注意注意

如果没有为 date 参数显式转换字符串文字,则使用日-月-年 (dmy) 日期格式的区域设置在 DATEADD 与其他日期/时间函数一起使用时可能会得到错误的结果。

返回 datetime2 类型

在 date 参数为 datetime2 类型时,DATEADD 返回 datetime2 类型。在将字符串文字用于 date 参数时,您必须显式将它们转换为 datetime2 类型,以便 DATEADD 返回 datetime2 类型。

datepart 参数

dayofyear、day 和 weekday 返回相同的值。

每个 datepart 及其缩写都返回相同的值。

如果 datepart 为 month 且 date 月份比返回月份的天数多,因而 date 中的日在返回月份中不存在,则返回返回月份的最后一天。例如,9 月份有 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 子句中。

秒的小数部分精度

不允许将日期部分microsecond 或 nanosecond 与数据类型为 smalldatetime、date 和 datetime 的 date 相加。

毫秒的小数位数为 3 (.123)。微秒的小数位数为 6 (.123456)。纳秒的小数位数为 9 (.123456789)。time、datetime2 和 datetimeoffset 数据类型的最大小数位数为 7 (.1234567)。如果 datepart 为 nanosecond,则 number 必须为 100 才能使 date 的秒小数部分增加。介于 1 和 49 之间的 number 向下舍入为 0,介于 50 和 99 之间的 number 向上舍入为 100。

以下语句加的 datepart 为 millisecond、microsecond 或 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);
/*
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 增加一级以上

下面的每条语句将 datepart 与一个足够大的 number 相加,使得 date 的上一级 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

下例将每个 OrderDate 加上 2 天以计算新的 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

下例指定 SYSDATETIME 用作 date。

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

将标量子查询和标量函数指定为 number 和 date

下例使用标量子查询和标量函数MAX(ModifiedDate) 作为 number 和 date 的参数。 (SELECT TOP 1 ContactID FROM Person.Contact) 是 number 形参的假实参,用来说明如何从值列表中选择 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. 将 DATEADD 用于使用 dmy 日期格式的区域设置

下面的示例演示对于某些区域设置如何将字符串文字用于 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