Share via

  • 10/01/2010

DATEADD (Transact-SQL)

Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.

For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). For information and examples that are common to date and time data types and functions, see Using Date and Time Data.

Topic link iconTransact-SQL Syntax Conventions

Syntax

DATEADD (datepart , number, date )

Arguments

  • datepart
    Is the part of date to which an integernumber is added. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

    datepart

    Abbreviations

    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
    Is an expression that can be resolved to an int that is added to a datepart of date. User-defined variables are valid.

    If you specify a value with a decimal fraction, the fraction is truncated and not rounded.

  • date
    Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal. If the expression is a string literal, it must resolve to a datetime. To avoid ambiguity, use four-digit years. For information about two-digit years, see two digit year cutoff Option.

Return Types

The return data type is the data type of the date argument, except for string literals.

The return data type for a string literal is datetime. An error will be raised if the string literal seconds scale is more than three positions (.nnn) or contains the time zone offset part.

Note

If string literals are not explicitly cast for the date parameter, then locals that use a day-month-year (dmy) date format may get incorrect results when DATEADD is used in conjunction with other date/time functions.

Returning a datetime2 type

DATEADD returns a datetime2 type when the date parameter is a datetime2 type. When using string literals for the date parameter, you must explicitly cast them to a datetime2 type for DATEADD to return a datetime2 type.

Return Value

datepart Argument

dayofyear, day, and weekday return the same value.

Each datepart and its abbreviations return the same value.

If datepart is month and the date month has more days than the return month and the date day does not exist in the return month, the last day of the return month is returned. For example, September has 30 days; therefore, the two following statements return 2006-09-30 00:00:00.000:

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

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

number Argument

The number argument cannot exceed the range of int. In the following statements, the argument for number exceeds the range of int by 1. The following error message is returned: "Arithmetic overflow error converting expression to data type int."

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

date Argument

The date argument cannot be incremented to a value outside the range of its data type. In the following statements, the number value that is added to the date value exceeds the range of the date data type. The following error message is returned: "Adding a value to a 'datetime' column caused overflow."

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

Return Values for a smalldatetime date and a second or Fractional Seconds datepart

The seconds part of a smalldatetime value is always 00. If date is smalldatetime, the following apply:

  • If datepart is second and number is between -30 and +29, no addition is performed.

  • If datepart is second and number is less than-30 or more than +29, addition is performed beginning at one minute.

  • If datepart is millisecond and number is between -30001 and +29998, no addition is performed.

  • If datepart is millisecond and number is less than -30001 or more than +29998, addition is performed beginning at one minute.

Remarks

DATEADD can be used in the SELECT <list>, WHERE, HAVING, GROUP BY and ORDER BY clauses.

Fractional Seconds Precision

Addition for a datepart of microsecond or nanosecond for date data types smalldatetime, date, and datetime is not allowed.

Milliseconds have a scale of 3 (.123). microseconds have a scale of 6 (.123456). nanoseconds have a scale of 9 (.123456789). The time, datetime2, and datetimeoffset data types have a maximum scale of 7 (.1234567). If datepart is nanosecond, number must be 100 before the fractional seconds of date increase. A number between 1 and 49 is rounded down to 0 and a number from 50 to 99 is rounded up to 100.

The following 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);
/*
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
*/

Time Zone Offset

Addition is not allowed for time zone offset.

Examples

A. Incrementing datepart by an interval of 1

Each of the following 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);
/*
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. Incrementing more than one level of datepart in one statement

Each of the following statements increments datepart by a number large enough to also 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.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. Using expressions as arguments for the number and date parameters

The following examples use different types of expressions as arguments for the number and date parameters.

Specifying column as date

The following example adds 2 days to each OrderDate to calculate a new PromisedShipDate.

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

Specifying user-defined variables as number and date

The following example specifies user-defined variables as arguments for number and 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);

Specifying scalar system function as date

The following example specifies SYSDATETIME for date.

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

Specifying scalar subqueries and scalar functions as number and date

The following example uses scalar subqueries and scalar functions, MAX(ModifiedDate), as arguments for number and date. (SELECT TOP 1 ContactID FROM Person.Contact) is an artificial argument for the number parameter to show how to select a number argument from a value list.

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

Specifying constants as number and date

The following example uses numeric and character constants as arguments for number and date.

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

Specifying numeric expressions and scalar system functions as number and date

The following example uses a numeric expressions (-(10/2)), unary operators (-), an arithmetic operator (/), and scalar system functions (SYSDATETIME) as arguments for number and date.

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

Specifying ranking functions as number

The following example uses a ranking function as arguments for 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;

Specifying an aggregate window function as number

The following example uses an aggregate window function as an argument for 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. Using DATEADD for Locales that use the dmy date format

The following examples show how to use string literals with DATEADD for some locales.

Demonstrating the pitfalls of using an implicit cast of a string literal

The following example shows what happens when a string literal is not explicitly cast.

SET LANGUAGE Español;

GO

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

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

GO

The first select statement returns julio (July) for the month and the second select statement return marzo (March) for the month.

Avoiding erroneous results by explicitly casting the string literal

The following example shows how to explicitly cast the date parameter to avoid erroneous results.

SET LANGUAGE Español;

GO

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

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

GO

Both select statements return marzo (March) for the month.

Using a datetime2 variable in the place of a string literal

The following example avoids the direct use of a string literal.

SET LANGUAGE Español;

GO

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

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

SELECT DATENAME(m, @d);

GO

See Also

Reference

CAST and CONVERT (Transact-SQL)