Using DATEADD and DATEDIFF to calculate SQL Server datetime values
In a SQL Server database, DATETIME and SMALLDATETIME values are stored as integers. However,
unlike integers, you 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 in order to determine the time interval
between them, such as the number of days or years. To let you easily perform these sorts of
calculations, Transact-SQL supports two important date/time functions: DATEADD and DATEDIFF.
In part four of my series on working with datetime values, I explain how to use these two
functions and provide examples
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in October 2008
of how they work. To demonstrate the functions, I used the following
Transact-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 create the table, I insert a single row of data into the table to
provide the necessary data for testing the DATEADD and DATEDIFF functions. Now let's take a look at
how these functions work.
Using the DATEADD 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
takes the following syntax:
DATEADD(<date/time_part>, <number>, <date>)
The <date/time_part> placeholder refers to the increment (such as day or 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 want to add an hour to a date/time value, 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 want to add 10 days to a date, you would specify 10. Note, however,
that if you want to subtract a time interval, you must specify a negative integer. For instance, to
subtract 10 days from the day, you would specify -10.
The <date> placeholder refers to the date/time value from 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 to demonstrate how this works. In the following SELECT statement, I add
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 August 27 has been changed to November 27. And you're not limited to
changing only dates. Here I add 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 are 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
subtract 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 will be 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 August 24, rather than August 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 add a row of
data to the Sales.Orders table and then use 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 use the GETDATE() function to retrieve the current date and time. For the DelivDate
column, I specify the DATEADD function, along with the expected three arguments. The first
argument, dd, indicates days will be added to the date. The second argument, 10, means 10 days will
be added. Finally, the last argument is again the GETDATE function. As a result, 10 days will be
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 subtract three days from the DelivDate value, and then display 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 use 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 will return
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 last one) added a row
with a DelivDate value of September 6. However, the value is now September 3, three days
earlier.
Using the DATEDIFF 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.
 |
| More on working with SQL Server datetime values: |
|
|
|
|
|
|
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 starting date you want to
measure, and the <end_date> placeholder refers to the ending date. In other
words, the function will return the specified time or date interval between the starting date and
the ending date.
Let's take a look at an example to demonstrate how that works. The following SELECT statement
calculates 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 use 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 starting date and the third argument specifies DelivDate as the ending date. As a result,
DATEDIFF will calculate the number of days between the OrderDate and DelivDate, which in this case
is 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 do 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 will return 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 use DATEDIFF to specify those rows where there are 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 function returns the number of days between the OrderDate
and DelivDate values. That number is then compared to 8. If the number of days is less than 8, the
row will be updated; otherwise, the row will not change. For the rows that are to be updated, I use
the DATEADD function to add three days to the DelivDate value. I then run a SELECT statement to
return the data from the Sales.Orders table and 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 create
a table that uses DATEADD and DATEDIFF, then add a row to the table and finally retrieve 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
In the CREATE TABLE statement, I create four columns, three of which store date/time data. The
OrderDate column simply uses GETDATE to generate a default value. The DelivDate column also
generates a default value. However, this one is based on the results returned by DATEADD, and in
this case, I use the function to add 10 days to the value returned by GETDATE and store that value
in the DelivDate column. Finally, the DaysDiff column is a computed column using DATEDIFF to
calculate the number of days between the OrderDate and DelivDate values.
After the table definition, I insert a row of data into the table. Because all the date/time
values are generated automatically, I need 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. For more details about either of these functions, refer to Microsoft SQL Server
Books Online.
Tip Series: Working with date/time data types in SQL
Server
Part 1: Basics for
working with DATETIME and SMALLDATETIME
Part 2: Data conversions
from date/time values to character types
Part 3: Using datetime
functions GETDATE, DATENAME and DATEPART
Part 4: Using DATEADD and DATEDIFF to calculate datetime values
Part 5: New datetime
data types in SQL Server 2008
ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and
training material related to Microsoft Windows, various relational database management systems, and
business intelligence design and implementation. You can find more information at http://www.rhsheldon.com.
MEMBER FEEDBACK TO THIS TIP
Do you have a comment on this tip? Let
us know.
What about daylight savings time? The T-SQL functions don't seem to be aware, that in most
places in the US, 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
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation