We help IT Professionals succeed at work.

Calculating the date difference in years

gringogordo
gringogordo asked
on
Medium Priority
1,339 Views
Last Modified: 2007-12-19
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
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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 DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.
Commented:
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
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.

Author

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.

Commented:
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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.