DateDiff(year,'12/31/2006','1/1/2007') is 1, as it crosses the 2006-2007 boundary.
DateDiff(year,'1/1/2006','12/31/2006') is 0 as it doesn't cross a year boundary.
I have written a function "yeardiff", which calculates the number of complete years between 2 dates, so
YearDiff('12/31/2006','1/1/2007') is 0
YearDiff('1/1/2006','12/31/2006') is 0
YearDiff('12/31/2006','1/1/2008') is 1
This function works by calculating the number of days the start date is into the year, subtracting this from both start and end dates (so the start date is 1 January), and calling DateDiff on the resulting date.
Here's the source code for this function:
CREATE FUNCTION yeardiff
(@start as datetime, @end as datetime)
returns int
AS
BEGIN
declare @years as int
declare @daysintoyear as int
declare @firstdayofyear as datetime
declare @newstart as datetime
declare @newend as datetime
set @firstdayofyear = convert(datetime,
'1/1/'+convert(varchar,year(@start)))
set @daysintoyear =datediff(day, @start, @firstdayofyear)
set @newstart = dateadd(dd, @daysintoyear,@start)
set @newend = dateadd(dd, @daysintoyear,@end)
return datediff(year,@newstart,@newend)
END
