Calculating the date difference in years


I think there must be a straightforward answer to this but its escaping me!

I want to work out the difference between 2 dates in years.  I don't want to use datediff('yy'... because this does a year() calculation so 01/jan/2001 is a year on from 31/dec/2000.  I want to do it on the basis that a year is 365 days.  so

01/jan/2001 - 31/dec/2000 = 0 years
01/jan/2001 - 31/dec/2001 = 1 year
22/mar/2005 - 21/mar/2005 = 1 year

and so on so basically I want to do a datediff(dd, start, end) / 365 but I would like to take account of the leep year.

Any suggestions?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Hi gringogordo,
use this function to check whether an year is leap year or not

CREATE FUNCTION [dbo].[ufn_IsLeapYear] ( @pDate DATETIME )
IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
YEAR( @pDate ) % 400 = 0
gringogordoAuthor Commented:
Thats a nice function but the problem for me is the date difference will be over multiple years (mainly 5 to 10) so I would have to use a cursor or something to loop round the years.  I don't like that thought much, I'm hoping there is a single statement that might do the job?
Scott PletcherSenior DBACommented:
Perhaps, although it will have some involved code in it.  You will need a table with all possible year numbers in them (1990, 1991, ..., 2006, ..., 2050): since a table of sequential numbers is so valuable anyway, you might as well create that.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

To calculate full years, use:

datediff(yy, 0, (MostRecentDate - PreviousDate) - 1)

Subtracting 1 accounts for leap year.  It adjusts the date to Mar 1 of non-Leap Years.  That is based on number of days from start of the year, days since last actual Feb 29, etc.

For ex:  
2/29/2004 to 2/28/2006 = 1
2/29/2004 to 3/1/2006 = 2


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
This is what I use.

DATEDIFF(yy, PreviousDate, MostRecentDate) +
        CASE WHEN (DATEPART(Month, MostRecentDate) < DATEPART(Month, PreviousDate)) THEN -1
             WHEN (DATEPART(Month, MostRecentDate) > DATEPART(Month, PreviousDate)) THEN 0
             WHEN (DATEPART(Day, MostRecentDate) < DATEPART(Day, PreviousDate)) THEN -1
             ELSE 0 END
Standard datediff, by year.  Then I Add a -1 to the year for those cases where the PreviousDate has not happened yet.
gringogordoAuthor Commented:
Sorry I must have been going mad.  In case someone is looking at this again

datediff(yy, 0, (MostRecentDate - PreviousDate) - 1)

doesn't work

SELECT datediff(yy, 0, (cast('31 Dec 2003' as datetime - cast('31 Dec 2002' as datetime) - 1)

comes back as 0 instead of 1.

Oops.  In my app, dates weren't inclusive.  Remove the "-1" from the end of the formula.  This will make 2/29/2004 to 2/28/2006 = 2, but in most instances, that will be acceptable.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.