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

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

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

select
 @days=abs(@Days)

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!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s