Tip

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 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

    Requires Free Membership to View

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


This was first published in October 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

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.