Calculate Duration between two dates (Month and Year will be Considered)

Question

I have two dates
06-Jan-2009 and 12-Dec-2010

I want to calculate the date difference between these two dates.. (basis on the month and year).
I want to get the answer 2 Year.

But if dates is 06-Jan-2009 And 12-Oct-2010
then I need 10 Months as output.

Solution 1

Try this one:

Declare @SDate DateTime ='06-Jan-2009'
Declare @EDate DateTime ='12-Oct-2009'


select case when DateDiff(M,@sDate,@EDate) <=12 
            then DateDiff(M,@sDate,@EDate) 
            else Round( ( Convert(Decimal(18,0)
                                   ,DateDiff(M,@sDate,@EDate)/12.0)),0) end

Solution 2

declare @Birthdate datetime 
declare @AsOnDate datetime

declare @years int
declare @months int 
declare @days int
declare @hours int
declare @minutes int 

--NOTE: date of birth must be smaller than As on date, 
--else it could produce wrong results
set @Birthdate = '1989-11-30 9:27 pm' --birthdate
set @AsOnDate  = Getdate()            --current datetime

--calculate years
select @years = datediff(year,@Birthdate,@AsOnDate)

--calculate months if it's value is negative then it 
--indicates after __ months; __ years will be complete
--To resolve this, we have taken a flag @MonthOverflow...
declare @monthOverflow int
select @monthOverflow = case when datediff(month,@Birthdate,@AsOnDate) - 
  ( datediff(year,@Birthdate,@AsOnDate) * 12) <0 then -1 else 1 end
--decrease year by 1 if months are Overflowed
select @Years = case when @monthOverflow < 0 then @years-1 else @years end
select @months =  datediff(month,@Birthdate,@AsOnDate) - (@years * 12) 

--as we do for month overflow criteria for days and hours 
--& minutes logic will followed same way
declare @LastdayOfMonth int
select @LastdayOfMonth =  datepart(d,DATEADD
    (s,-1,DATEADD(mm, DATEDIFF(m,0,@AsOnDate)+1,0)))

select @days = case when @monthOverflow<0 and 
    DAY(@Birthdate)> DAY(@AsOnDate) 
then @LastdayOfMonth + 
  (datepart(d,@AsOnDate) - datepart(d,@Birthdate) ) - 1  
      else datepart(d,@AsOnDate) - datepart(d,@Birthdate) end 

declare @hoursOverflow int
select @hoursOverflow = case when datepart(hh,@AsOnDate) -
    datepart(hh,@Birthdate) <0 then -1 else 1 end
select @hours = case when @hoursOverflow<0 then 24 + 
  datepart(hh,@AsOnDate) - datepart(hh,@Birthdate) 
  else datepart(hh,@AsOnDate) - datepart(hh,@Birthdate) end

declare @minutesOverflow int
select @minutesOverflow = case when datepart(mi,@AsOnDate) - 
    datepart(mi,@Birthdate) <0 then -1 else 1 end
select @minutes = case when @hoursOverflow<0 
    then 60 - (datepart(mi,@AsOnDate) - 
  datepart(mi,@Birthdate)) else abs(datepart
      (mi,@AsOnDate) - datepart(mi,@Birthdate)) end

select
 @Months=case when @days < 0 or DAY(@Birthdate)> DAY(@AsOnDate) then @Months-1 else @Months end

Declare @lastdayAsOnDate int;
set @lastdayAsOnDate = datepart(d,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@AsOnDate),0)));
Declare @lastdayBirthdate int;
set @lastdayBirthdate =  datepart(d,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Birthdate)+1,0)));

if (@Days < 0) 
(
    select @Days = case when( @lastdayBirthdate > @lastdayAsOnDate) then
        @lastdayBirthdate + @Days
    else
        @lastdayAsOnDate + @Days
    end
)
print  convert(varchar,@years)   + ' year(s),   '  +
       convert(varchar,@months)  + ' month(s),   ' +
       convert(varchar,@days)    + ' day(s),   '   +
       convert(varchar,@hours)   + ':'             + 
       convert(varchar,@minutes) + ' hour(s)' 

Comments

[Sandip]

I have updated my answer. You did not mention in your inital question