This documentation is archived and is not being maintained.

Comparing DATEADD and DATEDIFF

Updated: 14 April 2006

The DATEADD function adds an interval to a date you specify. For example, if the due dates of all orders in the SalesOrderHeader table slipped 3 days, you could obtain the new dates with the following statement:

USE AdventureWorks;
GO
SELECT DATEADD(day, 3, DueDate)
FROM Sales.SalesOrderHeader;
GO

If the date parameter is a smalldatetime data type, the result is also a smalldatetime. You can use DATEADD to add seconds or milliseconds to a smalldatetime value, but doing this is meaningful only if the result date returned by DATEADD changes by at least 1 minute.

The DATEDIFF function calculates the period of time in dateparts between the second and first of two dates you specify. In other words, it finds an interval between two dates. The result is a signed integer value equal to date2 - date1 in date parts.

The following query uses the date November 30, 2001, and finds the number of days that elapsed between DueDate and that date:

USE AdventureWorks;
GO
SELECT DATEDIFF(day, DueDate, 'Nov 30 2001')
FROM Sales.SalesOrderHeader;
GO

For the rows in SalesOrderHeader that have having a DueDate of October 21, 2001, the result produced by the last query is 40. (There are 40 days between October 21 and November 30.) To calculate an interval in months, use the following query:

USE AdventureWorks;
GO
SELECT interval = DATEDIFF(month, DueDate, 'Nov 30 2001')
FROM Sales.SalesOrderHeader;
GO

The query produces a value of 1 for the rows with a DueDate in October and a value of 5 for the rows with a DueDate in June.

When the first date in the DATEDIFF function is later than the second date specified, the resulting value is negative.

If one or both of the date arguments is a smalldatetime value, they are converted to datetime values internally for the calculation. Seconds and milliseconds in smalldatetime values are automatically set to 0 for calculation.

ms187081.note(en-US,SQL.90).gifNote:
DATEDIFF returns the number of date and time boundaries crossed between two specified dates. In the following example, the first statement returns 1 year. (That is, 2006 - 2005 = 1) The second statement returns 1 month. (That is, Jan 2006 - Dec 2005) The third statement returns 12 as the number of days between the two dates.

SELECT DATEDIFF(year, '20051220', '20060101')
SELECT DATEDIFF(month, '20051220', '20060101')
SELECT DATEDIFF(day, '20051220', '20060101')

Release History

14 April 2006

New content:
  • Added information about how DATEDIFF calculates the difference between date parts.

Show: