By: Tim Cullen | Updated: 2021-07-19 | Comments (17) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | > Dates
Problem
Date manipulation is a common scenario when retrieving or storing data in a Microsoft SQL Server database. There are several date functions (DATENAME, DATEPART, DATEADD, DATEDIFF, etc.) that are available and in this tutorial, we look at how to use the DATEADD function in SQL queries, stored procedures, T-SQL scripts, etc. for OLTP databases as well as data warehouse and data science projects.
Solution
The DATEADD function simply allows you to add or subtract the specified number of units of time to a specified date/time value.
SQL Server DATEADD Function
The T-SQL syntax of the DATEADD function is as follows:
-- Syntax to add 5 days to September 1, 2011 (input date) the function would be
DATEADD(DAY, 5, '9/1/2011')
-- Syntax to subtract 5 months from September 1, 2011 (input date) the function would be
DATEADD(MONTH, -5, '9/1/2011')
Using DATEADD Function and Examples
- Add 30 days to a date SELECT DATEADD(DD,30,@Date)
- Add 3 hours to a date SELECT DATEADD(HOUR,-3,@Date)
- Subtract 90 minutes from date SELECT DATEADD(MINUTE,-90,@Date)
- Check out the chart to get a list of all options
Date Formats and Units of Time
A thing to note is that the date format can be any date format that SQL Server recognizes such as:
- 9/1/2011
- 9/1/2011 12:30
- 9/1/2011 12:30:999
- 2011-09-01
- 2011-09-01 12:30
- etc...
Here are the units of time, the SQL Server versions in which they can be used, and abbreviations that can be used instead of the full unit of time name:
| Units of Time Available in SQL Server | |||
|---|---|---|---|
| Unit of time | 2000/2005 | 2008 + | Abbreviations |
| NANOSECOND | No | Yes | ns |
| MICROSECOND | No | Yes | mcs |
| MILLISECOND | Yes | Yes | ms |
| SECOND | Yes | Yes | ss, s |
| MINUTE | Yes | Yes | mi, n |
| HOUR | Yes | Yes | hh |
| WEEKDAY | Yes | Yes | dw, w |
| WEEK | Yes | Yes | wk, ww |
| DAY | Yes | Yes | dd, d |
| DAYOFYEAR | Yes | Yes | dy, y |
| MONTH | Yes | Yes | mm, m |
| QUARTER | Yes | Yes | qq, q |
| YEAR | Yes | Yes | yy, yyyy |
The table above reveals that there are some units of time that cannot be used with earlier versions of SQL Server. SQL Server 2008 and later introduced new date/time data types: DATETIME2, TIME, and DATETIMEOFFSET. The MICROSECOND and NANSECOND units of time were introduced as well, but cannot be used in earlier versions of SQL Server. Another thing to keep in mind is that you can't use the seconds unit of time with the DATE data type.
SQL Server DATEADD Function Examples
For all of these Transact-SQL examples, the parameter @Date = "2011-09-23 15:48:39.2370000", which we consider our current date. We can test the SQL commands as follows:
DECLARE @Date datetime2 = '2011-09-23 15:48:39.2370000' SELECT DATEADD(NANOSECOND,150000,@Date)
| Examples of Using the DATEADD Function | ||
|---|---|---|
| Unit of time | Query | Result |
| NANOSECOND | SELECT DATEADD(NANOSECOND,150000,@Date) | 2011-09-23 15:48:39.2371500 |
| MICROSECOND | SELECT DATEADD(MICROSECOND,150000,@Date) | 2011-09-23 15:48:39.3870000 |
| MILLISECOND | SELECT DATEADD(MILLISECOND,1500,@Date) | 2011-09-23 15:48:40.7370000 |
| SECOND | SELECT DATEADD(SECOND,1500,@Date) | 2011-09-23 16:13:39.2370000 |
| MINUTE | SELECT DATEADD(MINUTE,15,@Date) | 2011-09-23 16:03:39.2370000 |
| HOUR | SELECT DATEADD(HOUR,-3,@Date) | 2011-09-23 12:48:39.2370000 |
| WEEKDAY | SELECT DATEADD(WEEKDAY,-2,@Date) | 2011-09-21 15:48:39.2370000 |
| WEEK | SELECT DATEADD(WEEK,4,@Date) | 2011-10-21 15:48:39.2370000 |
| DAY | SELECT DATEADD(DD,4,@Date) | 2011-09-27 15:48:39.2370000 |
| DAYOFYEAR | SELECT DATEADD(DY,14,@Date) | 2011-10-07 15:48:39.2370000 |
| MONTH | SELECT DATEADD(MM,-4,@Date) | 2011-05-23 15:48:39.2370000 |
| QUARTER | SELECT DATEADD(Q,-2,@Date) | 2011-03-23 15:48:39.2370000 |
| YEAR |
SELECT DATEADD(YYYY,-5,@Date) | 2006-09-23 15:48:39.2370000 |
Example 1
A practical use of DATEADD is to return a user-friendly length of time that has elapsed between two times, like how long it takes for a student to complete an exam.
Below we are also using the DATEDIFF function to find the difference between the start and end time in seconds, then adding the number of seconds to 01/01/1900, which can be represented as a 0.
DECLARE @StartTime DATETIME = '2011-09-23 15:00:00'
,@EndTime DATETIME = '2011-09-23 17:54:02'
SELECT CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND,@StartTime, @EndTime),0), 108) as ElapsedTime
-- the output would be 02:54:02
Example 2
Another use would be when retrieving data based on a period. The procedure could pass in a start date and a number of days to retrieve data.
DECLARE @StartDate DATETIME = '2005-07-01' DECLARE @Units INT = 7 SELECT * FROM AdventureWorks2008R2.Production.WorkOrder WHERE StartDate BETWEEN @StartDate AND DATEADD(DAY, @Units, @StartDate)
Next Steps
- Keep in mind that the NANOSECOND and MICROSECOND units of time can only be used with SQL Server 2008 and later
- Also keep in mind that there is more than one way to execute a query and get the same results. There will be tips in the future that will discuss other options
- Read about the DATEADD function
- Learn more about the date and time data types available in SQL Server 2008 and later.
- For other date formats refer to this tip: Date and Time Conversions Using SQL Server
- Learn the options for the SQL current date including GETDATE(), CURRENT_TIMESTAMP, SYSDATETIME(), etc.
- Check out the following tutorials
About the author
Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-07-19
