DATEADD (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
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 Transact-SQL date and time data types and functions.
Transact-SQL syntax conventions
Syntax
DATEADD (datepart , number , date )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
datepart
The part of date to which DATEADD
adds an int number. This table lists all valid datepart arguments.
Note
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.
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 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. 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. 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, '20240731');
SELECT DATEADD(year, -2147483649, '20240731');
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. 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, '20240731');
SELECT DATEADD(year, -2147483647, '20240731');
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 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);
Here is the result set.
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 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);
Here is the result set.
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 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, 365, @datetime2); --2025-01-01 01:01:01.1111111
SELECT DATEADD(day, 365, @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 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
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());
Here is the result set.
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
Feedback
https://aka.ms/ContentUserFeedback.
Kommer snart: I hele 2024 udfaser vi GitHub-problemer som feedbackmekanisme for indhold og erstatter det med et nyt feedbacksystem. Du kan få flere oplysninger under:Indsend og få vist feedback om