Date Difference in Years, Months and Days in SQL Server

Recently, I had a requirement where I had to calculate the difference in Year(s), Months(s) and Day(s) between the two dates. Unfortunately there is no in-function in SQL Server to calculate the same. So, I wrote a user-defined function for this and thought of sharing the same on my blog. So, in this post, we will see how to calculate Date difference in Years, Months and dates between two dates.

SQL Server has in-built function DATEDIFF to find the difference between two dates, but, it works only on DATEPART provided in the first parameter of the function.

Let's take a look how it works
SET @date1='12/31/2010'
SET @date2='01/01/2011'
SELECT    datediff(YEAR,@date1,@date2) as years
        , datediff(MONTH,@date1,@date2) as months
        , datediff(DAY,@date1,@date2) as days

The actual difference between the two dates in only 1 day but look at the output it returns 1 year, 1 month and 1 day, which is incorrect as per our requirement. As per our requirement, it should be 0 year, 0 month and 1 day.

As a solution to above, I have created a user defined function to find the exact date difference in Year(s), Month(s) and Day(s) between two dates.
CREATE FUNCTION [dbo].[udfDateDiffinYrMonDay] (@datefrom DATETIME, @dateto DATETIME)
    DECLARE @Years INT, @Months INT, @Days INT
    SET @Years = DATEDIFF(YEAR, @datefrom, @dateto)
    IF DATEADD(YY, @Years, @datefrom) > @dateto
        SET @Years = @Years - 1

    SET @datefrom = DATEADD(YY, @Years, @datefrom)
    SET @Months = DATEDIFF(MM, @datefrom, @dateto)

    IF DATEADD(MM, @Months, @datefrom) > @dateto
        SET @Months = @Months - 1

    SET @datefrom = DATEADD(MM, @Months, @datefrom)
    SET @Days = DATEDIFF(DD, @datefrom, @dateto)

    RETURN CAST(@Years AS VARCHAR) + ' Years '
            + CAST(@Months AS VARCHAR) + ' Months '
            + CAST(@Days AS VARCHAR) + ' Days'

Let's test the above created function
SELECT dbo.udfDateDiffinYrMonDay('12/31/2010','01/01/2011')
SELECT dbo.udfDateDiffinYrMonDay('06/08/2006','01/01/2011')

Related post: calculate date difference in Year(s), Month(s) and Day(s) in C#

I am a Software Engineer and passionate programmer. Love working in SQL Server. Like participating in online technical communities like, MSDN, technical discussions and helping my friends, colleagues and others in their technical problems

Next Post »


March 4, 2016 at 9:10 PM delete

try the following dates 20090831, 20090830, 20090829. They all give same result

March 5, 2016 at 11:38 AM delete

Dear Tobi,

Thanks a lot for your valuable feeback.
I gave a deep thought on this. There is a twist in the logic, whether we should count the days of the start date month or the previous month of the end date. It completely change the picture, never thought of this earlier.
Lets take an example.
Start Date :01/25/2016
End Date :04/03/2016
In first scenario, the difference will be 1 month and 9 days (31-25)+3
In second scenario, the difference will be 1 month and 7 days (29-25)+3
Generally we take second scenario, so the logic is as per the second scenario.

In your case last date of previous month (i.e Feb) is 29, so it is giving the same result for each date >=29 for the Jan Month