Click here to Skip to main content
Click here to Skip to main content

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

By , 11 Jun 2013
Rate this:
Please Sign up or sign in to vote.

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 remain 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

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)' 

Happy coding!

Smile | :)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Aarti Meswania
Software Developer
India India
It's amazing maths of technology...
Expand knowledge by sharing it.

Comments and Discussions

 
QuestionGreat job! PinmemberMember 1049284125-Mar-14 20:53 
QuestionBug found PinmemberXoSkely104-Jul-13 0:29 
AnswerRe: Bug found PinmemberXoSkely104-Jul-13 0:45 
GeneralMy vote of 5 PinprofessionalSanjay K. Gupta29-Jun-13 1:10 
GeneralRe: My vote of 5 PinmemberAarti Meswania29-Jun-13 4:28 
GeneralMy vote of 5 PinprofessionalRaja Sekhar S26-Jun-13 19:14 
GeneralRe: My vote of 5 PinmemberAarti Meswania26-Jun-13 20:26 
GeneralRe: My vote of 5 PinprofessionalRaja Sekhar S26-Jun-13 20:44 
GeneralRe: My vote of 5 PinmemberAarti Meswania30-Jun-13 6:51 
GeneralMy vote of 3 PinmemberVitorHugoGarcia12-Jun-13 6:19 
SuggestionCompressed version PinprofessionalSunasara Imdadhusen20-May-13 21:15 
AnswerRe: Compressed version PinmemberAarti Meswania20-May-13 21:22 
BugProblem dates PinmemberMember 835067516-May-13 8:44 
AnswerRe: Problem dates (has been solved) PinmemberAarti Meswania19-May-13 23:50 
GeneralMy vote of 1 PinmemberMember 95384281-Jan-13 7:24 
QuestionRe: My vote of 1 PinmemberAarti Meswania1-Jan-13 17:34 
GeneralMy vote of 2 PinmemberURVISHSUTHAR31-Dec-12 21:08 
QuestionRe: My vote of 2 PinmemberAarti Meswania31-Dec-12 21:17 
AnswerRe: My vote of 2 PinmemberURVISHSUTHAR31-Dec-12 21:20 
Your result = "24 year(s), -10 month(s), -29 day(s), -1:4 hour(s)"
 
May I know why month, day and hour coming with minus sign Smile | :)
GeneralRe: My vote of 2 PinmemberAarti Meswania31-Dec-12 21:22 
GeneralRe: My vote of 2 PinmemberURVISHSUTHAR31-Dec-12 21:24 
AnswerRe: My vote of 2 PinmemberAarti Meswania31-Dec-12 21:30 
AnswerRe: My vote of 2 PinmemberAarti Meswania31-Dec-12 22:18 
GeneralRe: My vote of 2 PinmemberURVISHSUTHAR31-Dec-12 22:22 
GeneralRe: My vote of 2 PinmemberURVISHSUTHAR31-Dec-12 22:48 
AnswerRe: My vote of 2 PinmemberAarti Meswania31-Dec-12 22:52 
GeneralRe: My vote of 2 PinmemberURVISHSUTHAR31-Dec-12 23:04 
GeneralRe: My vote of 2 PinmemberAarti Meswania31-Dec-12 23:11 
GeneralRe: My vote of 2 PinmemberURVISHSUTHAR31-Dec-12 23:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 11 Jun 2013
Article Copyright 2012 by Aarti Meswania
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid