DateDiff, as already mentioned, only counts date boundaries crossed.

You'll get 1 if you query the datediff(year) for 1 second before midnight, on 31 Dec, and midnight, 1 Jan, because the year will be different by one. And so on for months, days, weeks, et al.

There are a couple of ways to have T-SQL determine if a full month has passed or not.

You can use variations on this:

DECLARE @StartDate DATETIME, @EndDate DATETIME;

SELECT @StartDate = '11/1/10', @EndDate = '12/7/10';

SELECT

CASE

WHEN DATEDIFF(MONTH, @StartDate, @EndDate) >= 1 AND DATEPART(DAY, @EndDate) >= DATEPART(DAY, @StartDate) THEN DATEDIFF(MONTH, @StartDate, @EndDate)

WHEN DATEDIFF(MONTH, @StartDate, @EndDate) >=1 AND DATEPART(DAY, @EndDate) < DATEPART(DAY, @StartDate) THEN DATEDIFF(MONTH, @StartDate, @EndDate) - 1

END;

If you have a Calendar table (a table with a list of dates and pre-calculated parts for them), you can write a version of this that will perform much better, since it won't have to do as many complex calculations on-the-fly.

Note that this version won't work if the End Date is earlier than the Start Date. For that, you'd need to add more conditions to the Case statement.