Realy year datediff (no year less)

I need to calcu开发者_如何转开发late the year diferences between two dates.

Tried using DATEDIFF but that only do the less of the dates, for example:

Date 1: 07/03/2011
Date 2: 07/02/2012

(Date format MM/DD/YYYY)

Then: DATEDIFF([yyyy], '07/03/2011', '07/02/2012') = 1

But the real diferences are 0 year.

This should work

declare @date1 datetime
declare @date2 datetime
select @date1 = '20110703', @date2 = '20120703'

select case
    when dateadd(yy, DATEDIFF(yy, @date1, @date2), @date1) > @date2
    then DATEDIFF(yy, @date1, @date2) -1
    else DATEDIFF(yy, @date1, @date2) end

A fuller test case showing many edge conditions

create table dates(id int identity, date1 datetime, date2 datetime)
insert dates select '20110703', '20120703'
insert dates select '20110703', '20120702'
insert dates select '20110702', '20120703'
insert dates select '20110228', '20120228'
insert dates select '20120229', '20130228'
insert dates select '20120229', '20130301'
insert dates select '20110301', '20120229'
insert dates select '20120229', '20160301'
insert dates select '20120229', '20160229'
insert dates select '20101231', '20110101'
insert dates select '20100101', '20111231'

select date1, date2,
    case
    when dateadd(yy, DATEDIFF(yy, date1, date2), date1) > date2
    then DATEDIFF(yy, date1, date2) -1
    else DATEDIFF(yy, date1, date2) end
from dates
order by id

Since noone had a correct solution i post mine even though noone will ever notice.

declare @d1 datetime
declare @d2 datetime

set @d1 = '1968-02-29'
set @d2 = '2011-02-28'

select datediff(year, @d1, @d2)-
case when month(@d1)*32 + day(@d1) > month(@d2) * 32 + day(@d2) then 1 else 0 end
--case when month(@d2)*32 + day(@d1) > month(@d2) * 32 + day(@d2) then 1 else 0 end

This method is basically the same as the next method, difference is that it is done with numbers to avoid the casting, which i was told was slower.

select datediff(year, @d1, @d2)- 
case when convert(char(5),@d1, 1) > convert(char(5),@d2, 1) then 1 else 0 end

SQL Server just does a year diff if you specify [yyyy].

For DATEDIFF([yyyy], '07/03/2011', '12/31/2011') it will return zero.

For DATEDIFF([yyyy], '07/03/2011', '01/01/2012') it will return 1.

In your case you should count the days if you are looking for 365 or 366 days:

DATEDIFF([dd], '07/03/2011', '07/02/2012') / 366

Yeah, DATEDIFF works that way with all dateparts, so I guess you should calculate the difference in days and then divide by 365 (this, if you don't care about the time of day). Si, try this:

DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @StarDate= '20110307'
SET @EndDate = '20120207'
SELECT DATEDIFF(DAY,@StartDate, @EndDate)/365

After reviewing the question and answers a bit more, all I've got to offer is some linkage: http://msdn.microsoft.com/en-us/library/ms189794.aspx.