An Introduction to the SQL Server T-SQL DATEADD Function
The DATEADD function adds (or subtracts) a whole-number increment to a specified date value. DATEADD is an incredibly useful and flexible built-in function to programmatically generate date values in your Transact-SQL (T-SQL) code.
Before I start, I want to show a commonly used example of how I’ve seen developers generate datetime values by adding days to an existing date value. In this example, the + operator is used to add one day to the current date (by calling the T-SQL GETDATE() function):
SELECT GETDATE() AS CurrentDate, GETDATE() + 1 AS TomorrowDate;
While this approach is perfectly valid for adding days to a date value, it’s not obvious a day is being added–it happens to be the default behavior of the + operator and a date. It’s incredibly important for developers to be very explicit in how they develop their code as it leads to ease of code maintenance and determinism. This is where the DATEADD function becomes useful as it gives much more control over the type of increments that are added to a date value, and so it should be used as a best practice for doing so.
As usual, most examples in this tutorial use the AdventureWorks sample database.
General Syntax
The general syntax of the DATEADD function is below. The function accepts three arguments, all of which are required:
DATEADD(datepart, number, date)
The datepart parameter is the increment you want to add or subtract to the provided date value parameter. I’ll discuss a number of these datepart increments throughout this article. The number parameter is a signed integer value you intend to add (or subtract if the specified number is negative) to the date passed in. The date parameter is an expression that can be one of the date (or time) data types in SQL Server. Generally, these values are datetime or date, but can also be datetime2, smalldatetime, datetimeoffset, or time data types.
The following example uses DATEADD to add a single DAY value to the current date, using GETDATE () to provide the input date.
SELECT DATEADD(DAY, 1, GETDATE()) AS TomorrowDate;
Functionally, it’s the same as the example in the introduction, but it’s easier to read and maintain while giving you more flexibility as a developer.
Below is a chart from Microsoft’s site detailing the different datepart values that can be passed into the DATEADD function as the options for increments to add or subtract when using the DATEADD function, anything from a year down to milliseconds, microseconds, or nanoseconds. You can provide the actual datepart keyword as the function parameter or supply the abbreviation. Personally, I prefer the verbose full name of the datepart option as opposed to supplying the abbreviation.
If I want to add a single day to the current date but without the timestamp included with datetime data types, I can use the CAST function to return a T-SQL DATE data type instead, which truncates off the timestamp, leaving only the date portion of the datetime (similar to creating an integer by truncating a decimal value). From there, a single day is added to the result of the call to the GETDATE () function, and its cast to the date data type, as shown below:
SELECT DATEADD(DAY, 1, CAST (GETDATE () AS DATE)) AS TomorrowDate;
Here’s an example using the WEEK datepart keyword to subtract seven days from the current date:
DECLARE @Today DATE = GETDATE (); SELECT DATEADD(WEEK, -1, @Today) AS OneWeekAgo;
A common use of most T-SQL functions is the ability to pass column values into them. The following example passes in the SalesOrderDetailID as the DAY number increment and the ModifiedDate as the date parameter, resulting in the virtual column NewDate:
SELECT TOP (10) SalesOrderDetailID, ModifiedDate, DATEADD(DAY, SalesOrderDetailID, ModifiedDate) AS NewDate FROM Sales.SalesOrderDetail ORDER BY SalesOrderDetailID ASC;
Complexity and Performance
Up to this point, I’ve shown simple examples of using the DATEADD function. While this is useful, I think it’s important to look at more complex examples and talk about how to nest function calls and a performance scenario as it relates to functions in the WHERE clause and index usage.
To begin, let’s calculate the number of days in the year so far. This example uses another of the SQL Server date functions, the DATEDIFF function, to calculate the number of days between the first of the year 2022 and today’s date. However, it’s only the number of days between today and the first of the year. To numerically include today’s day in the year, one day must be added to this value which could be done using a call to the DATEADD function, but let’s start this example simply and then build in the complexity.
DECLARE @DaysYTD SMALLINT; SELECT @DaysYTD = DATEDIFF (DAY, '1/1/2022', '5/16/2022') + 1 ; SELECT @DaysYTD AS DaysYTD;
A more succinct way to rewrite the above functionality is to use the DATEPART function with the DAYOFYEAR datepart to generate this same value, as shown below:
SELECT SalesOrderID, ABS (BINARY_CHECKSUM (rowguid)) AS GeneratedValue FROM Sales.SalesOrderDetail;
The next example generates some random numbers using the BINARY_CHECKSUM function on the rowguid uniqueidentifier column in the Sales.SalesOrderDetail table. BINARY_CHECKSUM accepts multiple values (the rowguid column in this case) and generates a signed integer value. Because the value is signed, the ABS function is used to return the absolute positive value.
SELECT SalesOrderID, ABS (BINARY_CHECKSUM (rowguid)) AS GeneratedValue FROM Sales.SalesOrderDetail;
The next step in generating some random date values is to use the @DaysYTD local variable (which holds the number of days in the year so far) and the modulus (%) operator to return a remainder value. These values will be between 0 and the value in @DaysYTD less one date.
SELECT SalesOrderID, (ABS (BINARY_CHECKSUM (rowguid)) % @DaysYTD) AS DaysToAdd FROM Sales.SalesOrderDetail;
Next, I’ll create a temp table named #SalesTable by using the SELECT INTO command. This code uses DATEADD to add the DaysToAdd random number calculation from above to the first day of the year, generating a random day value between the first of the year and today’s date.
SELECT SalesOrderID, DATEADD (DAY, (ABS (BINARY_CHECKSUM(rowguid)) % @DaysYTD), '1/1/2022') AS GeneratedDate INTO #SalesTable FROM Sales.SalesOrderDetail; SELECT * FROM #SalesTable;
I’m also going to create a non-clustered index on the GeneratedDate column and include the SalesOrderID, and as this is merely for demo purposes, there’s no need to cluster this table.
CREATE INDEX idx_SalesTableTemp_GeneratedDate ON #SalesTable(GeneratedDate) INCLUDE (SalesOrderID);
Next, let’s enable STATISTICS IO to see how many pages were read from the buffer pool for this query.
SET STATISTICS IO ON;
The aim of the query below is to find those rows having a GeneratedDate within the last seven days. The pattern in the WHERE clause is a programming practice I STILL see far too frequently in production environments–the use of functions on columns you’re setting criteria on. As a rule, any time you call a function on a column you’re filtering on, that function must be called for every row. There are, of course, caveats to this rule. However, this means that using this query pattern, an index on that column cannot use a Seek operation to filter out rows.
SELECT * FROM #SalesTable WHERE DATEADD (DAY, 7, GeneratedDate) > GETDATE ();
We can see from the execution plan a Table Scan occurs. Because the function must be called on every single row in the table, and then rows matching the criteria are filtered, the scan is the most efficient way to find the rows we are looking for.
Looking below at the number of logical reads from the query: there were 316 eight KB pages read from the SQL Server buffer pool to return the data for this query. Because this operation was a Table Scan, it means every page in this table was read during the operation.
Thankfully, there’s usually a way to refactor query predicates that call functions on columns with indexes. Here I’ve refactored the WHERE clause to isolate the GeneratedDate from the comparison operator. All I needed to do was to call DATEADD and subtract seven days from today’s date and then perform the date comparison.
SELECT * FROM #SalesTable WHERE GeneratedDate > DATEADD (DAY, -7, GETDATE ());
Looking at the execution plan for the above refactored query, we can see the Table Scan has been replaced with an Index Seek. The Index Seek means the values being searched for were found as efficiently as possible, and rows were returned without reading excessive data.
We can see from the output of STATISTICS IO only nine pages were read from the buffer pool for this Seek operation, compared to 316 pages from the Table Scan. This is really cool! A simple change to our WHERE clause completely changes the performance of the query–and this would have been MUCH more apparent had our table had many more records in it.
When to Use the DATEADD Function
The SQL Server DATEADD function adds or subtracts a number increment to a date value. This article showed several different examples of using DATEADD–from adding single days to a static date to calling the function on table values and, ultimately, a more complicated scenario generating dates based on random values and then using the DATEADD function to find those dates in the table having a week or less in age. DATEADD should be used instead of simply performing arithmetic on a datetime value. Remember, when comparing dates in a WHERE clause, don’t use the DATEADD function on a table column, as it will cause a costly table scan.
Simplify your ability to see the right information for answers to database performance problems. With SQL Sentry, you can get accurate, actionable, detailed metrics to quickly identify and address issues.