Problem solve Get 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.

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

More on working with SQL Server datetime values

Get SQL datetime questions and answers here

Visit our T-SQL section for more expert advice

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:

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.

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

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

Next Steps

How to calculate the difference between two dates in hours 

Figure out the difference in datetime columns by the minute

Learn to calculate weeks between two dates

This was last published in December 2016

Dig Deeper on SQL-Transact SQL (T-SQL)

Join the conversation

2 comments

Send me notifications when other members comment.

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

Please create a username to comment.

Which DATEADD and DATEDIFF SQL Server functions do you rely on the most?
Cancel
i want to find the day difference between second row of same column and first row of same column and third row of same column and second row of same column and so on... any body please help me to fix this.
Cancel

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close