Problem solveGet help with specific problems with your technologies, process and projects.
DATEADD and DATEDIFF SQL functions for datetime values
DATEADD and DATEDIFF SQL functions allow you to easily perform calculations, like adding a time interval from a datetime value. Here's how to use those functions for that and more.
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Step 2 of 2:
cannot simply use mathematical expressions to perform calculations on these values.
Even so, there might be times when you want to add or subtract a time interval from a date/time value. For example, you might want to add months or days to the value or, perhaps, even hours. You might even want to compare two date/time values to determine the time interval between them, such as the number of days or years. Transact-SQL (T-SQL) supports two important date/time functions -- DATEADD and DATEDIFF -- to enable you to easily perform these sorts of calculations.
In this, part four of my series on working with datetime values, I'll explain how to use these two functions and provide examples of how they work. To demonstrate the functions, I used the following T-SQL code to create the Sales.Orders table in the AdventureWorks sample database:
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, DelivDate DATETIME NOT NULL ) GO INSERT INTO Sales.Orders VALUES(1001, GETDATE(), '2008-09-08 18:27:10.750')
The table definition contains the OrderDate and DelivDate columns, both configured with the DATETIME data type. After I created the table, I inserted a single row of data into the table to provide it with the necessary data for testing the DATEADD and DATEDIFF SQL Server functions. Now, let's take a look at how these functions work.
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:
Date/time part
Abbreviations
year
yy, yyyy
quarter
qq, q
month
mm, m
day of year
dy, y
day
dd, d
week
wk, ww
weekday
dw
hour
hh
minute
mi, n
second
ss, s
millisecond
ms
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:
OrderDate
NewDate
2008-08-27 13:36:16.280
2008-11-27 13:36:16.280
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:
OrderDate
NewTime
2008-08-27 13:36:16.280
2008-08-27 16:36:16.280
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:
OrderDate
PastDate
2008-08-27 13:36:16.280
2008-08-24 13:36:16.280
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:
OrderID
OrderDate
DelivDate
1002
2008-08-27 13:40:22.357
2008-09-06 13:40:22.357
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:
OrderID
OrderDate
DelivDate
1002
2008-08-27 13:40:22.357
2008-09-03 13:40:22.357
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:
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.
Tip Series: Working with date/time data types in SQL Server
Part 4: Using DATEADD and DATEDIFF to calculate datetime values
Part 5: New datetime data types in SQL Server 2008
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:
OrderDate
DelivDate
DaysDiff
2008-08-27 13:40:22.357
2008-09-03 13:40:22.357
7
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:
OrderDate
DelivDate
HoursDiff
2008-08-27 13:40:22.357
2008-09-03 13:40:22.357
168
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.
OrderID
OrderDate
DelivDate
DaysDiff
1001
2008-08-27 13:36:16.280
2008-09-08 18:27:10.750
12
1002
2008-08-27 13:40:22.357
2008-09-06 13:40:22.357
10
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:
OrderID
OrderDate
DelivDate
DaysDiff
1001
2008-08-27 13:42:50.433
2008-09-06 13:42:50.433
10
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