The datediff function calculates the amount of time in date parts between the first and second of the two dates you specify—in other words, it finds the interval between the two dates. The result is a signed integer value equal to date2 - date1 in date parts.
This query uses the date November 30, 1990 and finds the number of days that elapsed between pubdate and that date:
select pubdate, newdate = datediff(day, pubdate,
"Nov 30 1990")
from titles
For the rows in the titles table having a pubdate of October 21, 1990, the result produced by the previous query is 40, the number of days between October 21 and November 30. To calculate an interval in months, the query is:
select pubdate, interval = datediff(month, pubdate,
"Nov 30 1990")
from titles
This query produces a value of 1 for the rows with a pubdate in October 1990 and a value of 5 for the rows with a pubdate in June 1990. When the first date in the datediff function is later than the second date, the resulting value is negative. Since 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 (-65) 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 calculating the difference.
