Get Duration Between Two Dates in Years, Months, Days, and Hours using SQL Server

Introduction

This code shows how to get years, months, days, and hours between two dates.

Using the Code

There is an example to set your birth-date and get the year, months, days, and hours as of current date-time...

Version I

Note: This will show output according to year change, e.g., 01-01-2012 to 01-01-2013.

Note: Date format should be yyyy-MM-dd

It can show months, days, and time in minus sign that indicates how many months-days-hours remains to complete a certain year.

declare @Birthdate datetime 
declare @AsOnDate datetime

declare @years varchar(4)
declare @months varchar(3) 
declare @days varchar(3)
declare @hours varchar(3)
declare @minutes varchar(2) 

set @Birthdate = '1989-11-30 9:27 AM' --birthdate
set @AsOnDate  = getdate()        --current datetime

select @years = datediff(year,@Birthdate,@AsOnDate)
select @months = datediff(month,@Birthdate,@AsOnDate) - 
	( datediff(year,@Birthdate,@AsOnDate) * 12) 
select @days = datepart(d,@AsOnDate) - datepart(d,@Birthdate) 
select @hours = datepart(hh,@AsOnDate) - datepart(hh,@Birthdate) 
select @minutes = abs(datepart(mi,@AsOnDate) - datepart(mi,@Birthdate))

print  @years   + ' year(s),   '  +
       @months  + ' month(s),   ' +
       @days    + ' day(s),   '   +
       @hours   + ':'             + @minutes + ' hour(s)'

Version II

Note: This will show the output according to the date of birth, e.g., 1989-11-30, 1990-11-30 , 1991-11-30 ...

Note: Date format should be yyyy-MM-dd

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

print  convert(varchar,@years)   + ' year(s),   '  +
       convert(varchar,@months)  + ' month(s),   ' +
       convert(varchar,@days)    + ' day(s),   '   +
       convert(varchar,@hours)   + ':'             + 
       convert(varchar,@minutes) + ' hour(s)'

Happy coding!