Calculating the date difference in years

Posted on 2006-03-27
Last Modified: 2007-12-19

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?

Question by:gringogordo
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    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
    RETURN 1
    RETURN 0

    Author Comment

    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?
    LVL 68

    Expert Comment

    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.
    LVL 7

    Accepted Solution

    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

    LVL 14

    Assisted Solution

    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
    LVL 14

    Expert Comment

    Standard datediff, by year.  Then I Add a -1 to the year for those cases where the PreviousDate has not happened yet.

    Author Comment

    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.

    LVL 7

    Expert Comment

    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.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Sql Permission 6 23
    Fixing Table alias inside a dynamic SQL query 2 20
    SQL Server 2008 Error 7 36
    Software suggestion 12 20
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now