Skip to content

dateadd-transact-sql.md

Latest commit

 

History

History
444 lines (320 loc) · 16.6 KB

dateadd-transact-sql.md

File metadata and controls

444 lines (320 loc) · 16.6 KB
title DATEADD (Transact-SQL)
description DATEADD returns a date modified by the specified date part.
author markingmyname
ms.author maghan
ms.reviewer randolphwest
ms.date 11/18/2025
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
DATEADD
DATEADD_TSQL
helpviewer_keywords
dates [SQL Server], functions
add interval to date or time [SQL Server]
subtract interval from date or time [SQL Server]
functions [SQL Server], time
functions [SQL Server], date and time
time [SQL Server], functions
dates [SQL Server], intervals
date and time [SQL Server], DATEADD
DATEADD function [SQL Server]
dev_langs
TSQL
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb

DATEADD (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]

::: moniker range="<=sql-server-ver16 || <=sql-server-linux-ver16"

This function adds a number (a signed integer) to a datepart of an input date, and returns a modified date/time value. For example, you can use this function to find the date that is 7,000 minutes from today: number = 7000, datepart = minute, date = today.

::: moniker-end

::: moniker range=">=sql-server-ver17 || >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || =azuresqldb-mi-current || =fabric || =fabric-sqldb"

This function adds a number (a signed bigint) to a datepart of an input date, and returns a modified date/time value. For example, you can use this function to find the date that is 7,000 minutes from today: number = 7000, datepart = minute, date = today.

::: moniker-end

See Date and time data types and functions for an overview of all [!INCLUDE tsql] date and time data types and functions.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

DATEADD ( datepart , number , date )

Arguments

datepart

::: moniker range="<=sql-server-ver16 || <=sql-server-linux-ver16"

The part of date to which DATEADD adds an int number.

::: moniker-end

::: moniker range=">=sql-server-ver17 || >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || =azuresqldb-mi-current || =fabric || =fabric-sqldb"

The part of date to which DATEADD adds a bigint number.

::: moniker-end

This table lists all valid datepart arguments. DATEADD doesn't accept user-defined variable equivalents for the datepart arguments.

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

::: moniker range="<=sql-server-ver16 || <=sql-server-linux-ver16"

An expression that can resolve to an int that DATEADD adds to a datepart of date. DATEADD accepts user-defined variable values for number. DATEADD truncates a specified number value that has a decimal fraction. It doesn't round the number value in this situation.

::: moniker-end

::: moniker range=">=sql-server-ver17 || >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || =azuresqldb-mi-current || =fabric || =fabric-sqldb"

An expression that can resolve to a bigint that DATEADD adds to a datepart of date. DATEADD accepts user-defined variable values for number. DATEADD truncates a specified number value that has a decimal fraction. It doesn't round the number value in this situation.

::: moniker-end

date

An expression that can resolve to one of the following values:

  • date
  • datetime
  • datetimeoffset
  • datetime2
  • smalldatetime
  • time

For date, DATEADD accepts a column expression, expression, string literal, or user-defined variable. A string literal value must resolve to a datetime. Use four-digit years to avoid ambiguity issues. See Server configuration: two digit year cutoff for information about two-digit years.

Return types

The return value data type for this method is dynamic. The return type depends on the argument supplied for date. If the value for date is a string literal date, DATEADD returns a datetime value. If another valid input data type is supplied for date, DATEADD returns the same data type. 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 argument

dayofyear, day, and weekday return the same value.

Each datepart and its abbreviations return the same value.

If the following are true:

  • datepart is month
  • the date month has more days than the return month
  • the date day doesn't exist in the return month

Then, DATEADD returns the last day of the return month. For example, September has 30 (thirty) days; therefore, these statements return 2024-09-30 00:00:00.000:

SELECT DATEADD(month, 1, '20240830');
SELECT DATEADD(month, 1, '2024-08-31');

number argument

::: moniker range="<=sql-server-ver16 || <=sql-server-linux-ver16"

The number argument can't exceed the range of int. In the following statements, the argument for number exceeds the range of int by 1.

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

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.

::: moniker-end

::: moniker range=">=sql-server-ver17 || >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || =azuresqldb-mi-current || =fabric || =fabric-sqldb"

The number argument can't exceed the range of bigint. In the following statements, the argument for number exceeds the range of bigint by 1.

DECLARE @datetime2 AS DATETIME2;
SET @datetime2 = '2025-11-01 01:01:01.1111111';
SELECT DATEADD(nanosecond, 9223372036854775808, @datetime2);
SELECT DATEADD(nanosecond, -9223372036854775809, @datetime2);
GO

These statements both return the following error message:

Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type bigint.

::: moniker-end

date argument

DATEADD doesn't accept a date argument incremented to a value outside the range of its data type. In the following statements, the number value added to the date value exceeds the range of the date data type.

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

DATEADD returns the following error message:

Msg 517, Level 16, State 1
Line 1 Adding a value to a 'datetime' column caused overflow.

Return values for a smalldatetime date and a second or fractional seconds datepart

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

  • For a datepart of second, and a number value between -30 and +29, DATEADD makes no changes.

  • For a datepart of second, and a number value less than -30, or more than +29, DATEADD performs its addition beginning at one minute.

  • For a datepart of millisecond and a number value between -30001 and +29998, DATEADD makes no changes.

  • For a datepart of millisecond and a number value less than -30001, or more than +29998, DATEADD performs its addition beginning at one minute.

Remarks

Use DATEADD in the following clauses:

  • GROUP BY
  • HAVING
  • ORDER BY
  • SELECT <list>
  • WHERE

Fractional seconds precision

DATEADD doesn't allow addition for a datepart of microsecond or nanosecond for date data types smalldatetime, date, and datetime.

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

These statements add a datepart of millisecond, microsecond, or nanosecond.

DECLARE @datetime2 AS DATETIME2 = '2024-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);

[!INCLUDE ssResult]

1 millisecond     2024-01-01 13:10:10.1121111
2 milliseconds    2024-01-01 13:10:10.1131111
1 microsecond     2024-01-01 13:10:10.1111121
2 microseconds    2024-01-01 13:10:10.1111131
49 nanoseconds    2024-01-01 13:10:10.1111111
50 nanoseconds    2024-01-01 13:10:10.1111112
150 nanoseconds   2024-01-01 13:10:10.1111113

Time zone offset

DATEADD doesn't allow addition for time zone offset.

Examples

A. Increment datepart by an interval of 1

Each of these statements increments datepart by an interval of 1:

DECLARE @datetime2 AS DATETIME2 = '2024-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);

[!INCLUDE ssResult]

year         2025-01-01 13:10:10.1111111
quarter      2024-04-01 13:10:10.1111111
month        2024-02-01 13:10:10.1111111
dayofyear    2024-01-02 13:10:10.1111111
day          2024-01-02 13:10:10.1111111
week         2024-01-08 13:10:10.1111111
weekday      2024-01-02 13:10:10.1111111
hour         2024-01-01 14:10:10.1111111
minute       2024-01-01 13:11:10.1111111
second       2024-01-01 13:10:11.1111111
millisecond  2024-01-01 13:10:10.1121111
microsecond  2024-01-01 13:10:10.1111121
nanosecond   2024-01-01 13:10:10.1111111

B. Increment more than one level of datepart in one statement

Each of these statements increments datepart by a number large enough to additionally increment the next higher datepart of date:

DECLARE @datetime2 AS DATETIME2;

SET @datetime2 = '2024-01-01 01:01:01.1111111';

--Statement                                 Result
-------------------------------------------------------------------
SELECT DATEADD(quarter, 4, @datetime2);     --2025-01-01 01:01:01.1111111
SELECT DATEADD(month, 13, @datetime2);      --2025-02-01 01:01:01.1111111
SELECT DATEADD(dayofyear, 366, @datetime2); --2025-01-01 01:01:01.1111111
SELECT DATEADD(day, 366, @datetime2);       --2025-01-01 01:01:01.1111111
SELECT DATEADD(week, 5, @datetime2);        --2024-02-05 01:01:01.1111111
SELECT DATEADD(weekday, 31, @datetime2);    --2024-02-01 01:01:01.1111111
SELECT DATEADD(hour, 23, @datetime2);       --2024-01-02 00:01:01.1111111
SELECT DATEADD(minute, 59, @datetime2);     --2024-01-01 02:00:01.1111111
SELECT DATEADD(second, 59, @datetime2);     --2024-01-01 01:02:00.1111111
SELECT DATEADD(millisecond, 1, @datetime2); --2024-01-01 01:01:01.1121111

C. Use expressions as arguments for the number and date parameters

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

Specify a column as date

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;

Here's 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

Specify user-defined variables as number and date

This example specifies user-defined variables as arguments for number and date:

DECLARE @days AS INT = 365,
        @datetime AS DATETIME = '2000-01-01 01:01:01.111' /* 2000 was a leap year */;

SELECT DATEADD(day, @days, @datetime);

[!INCLUDE ssResult]

2000-12-31 01:01:01.110

Specify scalar system function as date

This example specifies SYSDATETIME for date. The exact value returned depends on the day and time of statement execution:

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

[!INCLUDE ssResult]

2024-04-25 14:29:59.6727944

Specify scalar subqueries and scalar functions as number and date

This example uses scalar subqueries, MAX(ModifiedDate), as arguments for number and date. (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)
);

Specify numeric expressions and scalar system functions as number and date

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

Specify ranking functions as 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;

Specify an aggregate window function as 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

Related content