dateadd-transact-sql.md

title	description	author	ms.author	ms.reviewer	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs	monikerRange
DATEADD (Transact-SQL)	DATEADD returns a date modified by the specified date part.	markingmyname	maghan	randolphwest	02/25/2025	sql	t-sql	reference	DATEADD DATEADD_TSQL	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]	TSQL	>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

DATEADD (Transact-SQL)

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

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.

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

The part of date to which DATEADD adds an intnumber.

Note

In [!INCLUDE ssazure-sqldb] and [!INCLUDE fabric], number can be expressed as a bigint. This feature is in preview.

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

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.

Note

In [!INCLUDE ssazure-sqldb] and [!INCLUDE fabric], number can be expressed as a bigint. This feature is in preview.

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

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. 

Note

In [!INCLUDE ssazure-sqldb] and [!INCLUDE fabric], number can be expressed as a bigint. This feature is in preview.

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 smalldatetimedate 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 ALLSELECT'2 milliseconds', DATEADD(millisecond, 2, @datetime2) UNION ALLSELECT'1 microsecond', DATEADD(microsecond, 1, @datetime2) UNION ALLSELECT'2 microseconds', DATEADD(microsecond, 2, @datetime2) UNION ALLSELECT'49 nanoseconds', DATEADD(nanosecond, 49, @datetime2) UNION ALLSELECT'50 nanoseconds', DATEADD(nanosecond, 50, @datetime2) UNION ALLSELECT'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 ALLSELECT'quarter', DATEADD(quarter, 1, @datetime2) UNION ALLSELECT'month', DATEADD(month, 1, @datetime2) UNION ALLSELECT'dayofyear', DATEADD(dayofyear, 1, @datetime2) UNION ALLSELECT'day', DATEADD(day, 1, @datetime2) UNION ALLSELECT'week', DATEADD(week, 1, @datetime2) UNION ALLSELECT'weekday', DATEADD(weekday, 1, @datetime2) UNION ALLSELECT'hour', DATEADD(hour, 1, @datetime2) UNION ALLSELECT'minute', DATEADD(minute, 1, @datetime2) UNION ALLSELECT'second', DATEADD(second, 1, @datetime2) UNION ALLSELECT'millisecond', DATEADD(millisecond, 1, @datetime2) UNION ALLSELECT'microsecond', DATEADD(microsecond, 1, @datetime2) UNION ALLSELECT'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.1111111SELECT DATEADD(month, 13, @datetime2); --2025-02-01 01:01:01.1111111SELECT DATEADD(dayofyear, 366, @datetime2); --2025-01-01 01:01:01.1111111SELECT DATEADD(day, 366, @datetime2); --2025-01-01 01:01:01.1111111SELECT DATEADD(week, 5, @datetime2); --2024-02-05 01:01:01.1111111SELECT DATEADD(weekday, 31, @datetime2); --2024-02-01 01:01:01.1111111SELECT DATEADD(hour, 23, @datetime2); --2024-01-02 00:01:01.1111111SELECT DATEADD(minute, 59, @datetime2); --2024-01-01 02:00:01.1111111SELECT DATEADD(second, 59, @datetime2); --2024-01-01 01:02:00.1111111SELECT 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 FROMSales.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 ASINT=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 FROMPerson.Person), (SELECTMAX(ModifiedDate) FROMPerson.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.

SELECTp.FirstName, p.LastName, DATEADD(day, ROW_NUMBER() OVER (ORDER BYa.PostalCode), SYSDATETIME()) AS'Row Number'FROMSales.SalesPersonAS s INNER JOINPerson.PersonAS p ONs.BusinessEntityID=p.BusinessEntityIDINNER JOINPerson.AddressAS a ONa.AddressID=p.BusinessEntityIDWHERE TerritoryID IS NOT NULLAND 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'FROMSales.SalesOrderDetailWHERE SalesOrderID IN (43659, 43664); GO

Related content

• CAST and CONVERT (Transact-SQL)