Link to home
Start Free TrialLog in
Avatar of gringogordo
gringogordo

asked on

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
SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gringogordo
gringogordo

ASKER

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?
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.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Standard datediff, by year.  Then I Add a -1 to the year for those cases where the PreviousDate has not happened yet.
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.