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';
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
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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon