DATEADD and DATEDIFF SQL functions for datetime values

Using the DATEADD SQL Server function

In some circumstances, you might want to add an interval of time to a DATETIME or SMALLDATETIME value -- or subtract an interval of time. For example, you might need to add or subtract a month from a specific date. You can use the DATEADD function to perform this calculation. The function uses the following syntax:

DATEADD(<date/time_part>, <number>, <date>)

The <date/time_part> placeholder refers to the increment (such as a day or a month) you want to add or subtract from a date/time value. The following table lists the date/time parts you can use, along with the abbreviations that represent those parts:

For example, if you wanted to add an hour to a date/time value, you would use the hh abbreviation. In some cases, the date/time part supports two abbreviations, such as week, which supports either wk or ww.

The <number> placeholder refers to the value (an integer) by which to increment the date. For instance, if you wanted to add 10 days to a date, you would specify 10. Note, however, that if you wanted to subtract a time interval, you would have to specify a negative integer. For instance, to subtract 10 days from the date, you would specify -10.

The <date> placeholder refers to the date/time value to which you want to add or subtract the specified interval. This can be a string value in a date/time format, a date/time value returned by a function, or, as is often the case, a DATETIME or SMALLDATETIME column.

Let's look at an example. In the following SELECT statement, I added three months to the OrderDate value in the Sales.Orders table:

SELECT OrderDate, DATEADD(mm, 3, OrderDate) AS NewDate
FROM Sales.Orders
WHERE OrderID = 1001

Notice that the SELECT list includes the DATEADD function. The function takes three arguments: mm refers to months, 3 refers to the number of months and OrderDate is the DATETIME value. As a result, three months will be added to the OrderDate value when the query returns the value, as shown in the following results:

As you can see, the date, Aug. 27, was changed to Nov. 27. And you're not limited to only changing dates. Here, I added three hours to the OrderDate value:

SELECT OrderDate, DATEADD(hh, 3, OrderDate) AS NewTime
FROM Sales.Orders
WHERE OrderID = 1001

The first argument in DATEADD is now hh, rather than mm, so only the hours were changed, as the following results show:

You can also subtract date or time intervals from a date/time value. In the following example, I subtracted three days from the OrderDate value:

SELECT OrderDate, DATEADD(dd, -3, OrderDate) AS PastDate
FROM Sales.Orders
WHERE OrderID = 1001

Notice that the first DATEADD argument now specifies dd. Also notice that the second argument is a negative number, meaning three days were subtracted, as the shown here:

As you can see, the new date is Aug. 24, rather than Aug. 27.

Up to this point, the examples have shown you how to modify a date/time value as you retrieve it from the database. You can also use the DATEADD function to insert date/time data. This is because the DATEADD function returns a DATETIME value as its output. (It will return a SMALLDATETIME value if the date provided to the function is SMALLDATETIME.) In the following example, I added a row of data to the Sales.Orders table, and then used a SELECT statement to retrieve that row:

INSERT INTO Sales.Orders
VALUES(1002, GETDATE(), DATEADD(dd, 10, GETDATE()))
GO
SELECT * FROM Sales.Orders
WHERE OrderID = 1002

Notice that the VALUES clause includes a value for each column in the table. For the OrderDate value, I used the GETDATE() function to retrieve the current date and time. For the DelivDate column, I specified the DATEADD function, along with the expected three arguments. The first argument, dd, indicates the days to be added to the date. The second argument, 10, indicates 10 days will be added. Finally, the last argument is, again, the GETDATE function. As a result, 10 days were added to the current date and time and inserted into the DelivDate column. Here are the results generated by the SELECT statement:

As expected, the DelivDate value is 10 days later than the OrderDate value.

Now, let's check out an UPDATE statement that uses the DATEADD function. In the following statements, I subtracted three days from the DelivDate value and then displayed the results:

UPDATE Sales.Orders
SET DelivDate = DATEADD(dd, -3, DelivDate)
WHERE OrderID = 1002
GO
SELECT * FROM Sales.Orders
WHERE OrderID = 1002

This time, I used DATEADD in the SET clause -- I set DelivDate to equal the results returned by the DATEADD function. The function specifies days (dd) for the first argument, -3 for the second argument and the DelivDate column for the third argument. This means that the function returned a date three days earlier than the original date and set the DelivDate column to the new date, as shown in the following results:

As you'll recall, the INSERT statement (in the example that preceded this one) added a row with a DelivDate value of Sept. 6. However, the value is now Sept. 3, three days earlier.

Using the DATEDIFF SQL Server function

The DATEDIFF function calculates the time interval between two dates and returns an integer that represents the interval. The function takes the following syntax:

DATEDIFF(<date/time_part>, <start_date>, <end_date>)

The <date/time_part> placeholder refers to the time increment you want to measure between two dates. For example, you might want to determine the number of hours or days between a start date and end date. 

The <date/time_part> placeholder uses the same abbreviations you used for the DATEADD function, except for the weekday (dw, w) abbreviations. The weekday option is not supported for DATEDIFF.

The <start_date> placeholder refers to the start date of the span of time you want to measure, and the <end_date> placeholder refers to its end date. In other words, the function will return the specified time or date interval between the start date and the end date.

Let's take a look at an example. The following SELECT statement calculated the time interval between the OrderDate and DelivDate values in the Sales.Orders table:

SELECT OrderDate, DelivDate,
DATEDIFF(dd, OrderDate, DelivDate) AS DaysDiff
FROM Sales.Orders
WHERE OrderID = 1002

In this statement, I used DATEDIFF as one of the elements in the SELECT list. The function's first argument specifies that the interval should be in days (dd), the second specifies OrderDate as the start date and the third argument specifies DelivDate as the end date. As a result, DATEDIFF calculated the number of days between the OrderDate and the DelivDate, which, in this case, was seven days, as you can see in the following results:

Of course, you can also calculate one of the time-related intervals, as I did in the following statement:

SELECT OrderDate, DelivDate,
DATEDIFF(hh, OrderDate, DelivDate) AS HoursDiff
FROM Sales.Orders
WHERE OrderID = 1002

In this case, the function's first argument is hours (hh), rather than days. As a result, the function returned the number of hours between the OrderDate and the DelivDate values, as shown in the following results:

There is a difference of 168 hours between the two values.

Like the DATEADD function, the DATEDIFF function is not limited to SELECT statements. For instance, you can use DATEDIFF in the WHERE clause of an UPDATE statement to determine which rows to update. In the following example, I used DATEDIFF to specify those rows where there were fewer than eight days between the OrderDate and DelivDate values:

UPDATE Sales.Orders
SET DelivDate = DATEADD(dd, 3, DelivDate)
WHERE DATEDIFF(dd, OrderDate, DelivDate) < 8
GO
SELECT OrderID, OrderDate, DelivDate,
DATEDIFF(dd, OrderDate, DelivDate) AS DaysDiff
FROM Sales.Orders

In the earlier examples, the DATEDIFF SQL Server function returned the number of days between the OrderDate and the DelivDate values. That number was then compared to 8. If the number of days was less than 8, the row was updated; otherwise, the row did not change. For the rows that were updated, I used the DATEADD function to add three days to the DelivDate value. I then ran a SELECT statement to return the data from the Sales.Orders table and to calculate the difference between the two dates in each row, as shown in the following results.

The results show there is now a 10-day interval between the two dates (in the second row), rather than the original seven-day interval.

Using DATEADD and DATEDIFF in table definitions

DATEADD and DATEDIFF functions can also be used within your table definitions. You could, for example, use the DATEADD function in the DEFAULT clause of the column definition, or use the DATEDIFF function to create a calculated column. In the following Transact-SQL code, I first created a table that used DATEADD and DATEDIFF, then added a row to the table and, finally, retrieved data from the table:

USE AdventureWorks
GO
IF EXISTS (SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'Sales'
AND table_name = 'Orders')
DROP TABLE Sales.Orders
GO
CREATE TABLE Sales.Orders
(
OrderID INT NOT NULL,
OrderDate DATETIME NOT NULL DEFAULT GETDATE(),
DelivDate DATETIME NOT NULL DEFAULT DATEADD(dd, 10, GETDATE()),
DaysDiff AS DATEDIFF(dd, OrderDate, DelivDate)
)
GO
INSERT INTO Sales.Orders(OrderID)
VALUES(1001)
GO
SELECT OrderID, OrderDate, DelivDate, DaysDiff
FROM Sales.Orders

The DATEADD and DATEDIFF functions are useful not only in table definitions, but in your queries and data modification statements, as well.

In the CREATE TABLE statement, I created four columns, three of which stored date/time data. The OrderDate column simply used GETDATE to generate a default value. The DelivDate column also generated a default value. However, this one was based on the results returned by DATEADD, and in this case, I used the function to add 10 days to the value returned by GETDATE and stored that value in the DelivDate column. Finally, the DaysDiff column used DATEDIFF to calculate the number of days between the OrderDate and DelivDate values.

After the table definition, I inserted a row of data into the table. Because all of the date/time values were generated automatically, I needed to insert only the OrderID value, as shown here:

The DATEADD and DATEDIFF functions are useful not only in table definitions, but in your queries and data modification statements, as well. With DATEADD, you can add and subtract a specified interval from a date/time value, and with DATEDIFF, you can calculate the time interval between two date/time values. To learn more about using these values or applying DATEDIFF or DATEADD to Azure SQL Database or Azure SQL Data Warehouse, visit Microsoft's Developer Network site.

About the author:
Robert Sheldon is a technical consultant and the author of numerous books, articles and training materials related to Microsoft Windows, various relational database management systems and business intelligence design and implementation. You can find more information about him at http://www.rhsheldon.com.

Member feedback to this tip
Do you have a comment on this tip? Let us know.

What about daylight saving time? The T-SQL functions don't seem to be aware, that in most places in the U.S., there were only 23 hours in March 9, 2008. If it were time zone-aware, then DATEADD(hh, 12, '2008-03-09 00:00') would return '2008-03-09 13:00' instead of '2008-03-09 12:00' 47.5 states.
Cort B.

That's right, the DATETIME function does not take into account time zone. Here's a Microsoft article on preparing SQL Server for changes to daylight saving time that shows how to use the GETUTCDATE() function as a workaround.
Robert Sheldon, Writer