Calculating the date difference in years

Hi,  

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?

Thanks
gringogordoAsked:
Who is Participating?
 
LandyJCommented:
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

hth,
Landy
0
 
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 )
RETURNS BIT
AS
BEGIN
IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
YEAR( @pDate ) % 400 = 0
RETURN 1
RETURN 0
END
GO
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?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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.
0
 
adwisemanCommented:
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
0
 
adwisemanCommented:
Standard datediff, by year.  Then I Add a -1 to the year for those cases where the PreviousDate has not happened yet.
0
 
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.

0
 
LandyJCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.