Comparing DATEADD and DATEDIFF

The DATEADD function adds an interval to a date you specify. For example, if the publication dates of all books in the titles table slipped three days, you could get the new publication dates with this statement:

USE pubs

SELECT DATEADD(day, 3, pubdate)

FROM titles

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

This query uses the date November 30, 1995, and finds the number of days that elapsed between pubdate and that date:

USE pubs

SELECT DATEDIFF(day, pubdate, 'Nov 30 1995')

FROM titles

For the rows in titles having a pubdate of October 21, 1995, 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 this query:

USE pubs

SELECT interval = DATEDIFF(month, pubdate, 'Nov 30 1995')

FROM titles

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

When the first date in the DATEDIFF function is later than the second date specified, the resulting value is negative. Because two of the rows in titles have values for pubdate that are assigned using the GETDATE function as a default, these values are set to the date that your pubs database was created and return negative values in the two preceding queries.

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 the purpose of calculation.

See Also