how to get date difference in years month and date

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » how to get date difference in years month and...

how to get date difference in years month and date

Rate Topic

Display Mode

Topic Options

Author

Message

kuppurajm

Posted Wednesday, January 05, 2011 1:56 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, July 09, 2012 1:43 AM
Points: 49, Visits: 198

Hi all,
how to get difference in year month and days from 2 date values?
Ex:10/aug/2010,12/oct/2010 then result is
Res:0 year,2 month,2 days

Post #1042865

Junglee_George

Posted Wednesday, January 05, 2011 2:12 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 6:23 AM
Points: 477, Visits: 557

Hi..Try this

select cast(datediff(yyyy, '2006-01-01', '2008-01-01') as varchar) + 'Y : '

+ cast(datediff(mm, '2006-01-01', '2008-01-01') as varchar) + 'M : '

+ cast(datediff(dd, '2006-01-01', '2008-01-01') as varchar) + 'D'

Post #1042874

Evil Kraig F

Posted Wednesday, January 05, 2011 2:24 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:35 PM
Points: 5,722, Visits: 6,194

Junglee_George (1/5/2011)

Right idea, Junglee, but you're going to get 24 months there, when it should be 2 years, 0 months, 0 days.

You'll need to do something like this:

DECLARE @date1 DATETIME,
		@date2 DATETIME

SELECT @date1 = '1/1/2008',
		@date2 = '4/12/2010'

SELECT
	DATEDIFF( mm, @date1, @date2) / 12 AS years
	, DATEDIFF( mm, @date1, @date2) % 12 AS months
	, DATEDIFF( dd, DATEADD( mm, DATEDIFF( mm, @date1, @date2), @date1), @date2)

Because we're not looking for the actual year crossing of 12/31 - 1/1, you need to derive the # of years from the # of months differentiating the values. Then the # of months is the remainder of the division by 12. The reason for the dateadd/datediff for days is that we have to advance the @date1 forward to just give us a difference in days.

Problem is, this is incomplete. It won't handle inverted days well. To see what I mean, invert the date1/2 to this:

SELECT @date1 = '4/12/2008',
		@date2 = '1/1/2010'

If the above solves your problem, well and good. If not, I've got to go find some code that I wrote up at one point to deal with this. It gets really messy.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1042879

kuppurajm

Posted Wednesday, January 05, 2011 2:46 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, July 09, 2012 1:43 AM
Points: 49, Visits: 198

It give result like 0Y : 2M : 63D
I need the result like:2M:2days (2010-08-10,2010-10-12)

Post #1042890

kuppurajm

Posted Wednesday, January 05, 2011 3:02 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, July 09, 2012 1:43 AM
Points: 49, Visits: 198

Thank you craig.
It may solve my issus

Post #1042900

raj_yash22

Posted Thursday, February 03, 2011 3:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 07, 2011 6:53 AM
Points: 2, Visits: 2

try this

--By Rajat Bhalla
ALTER FUNCTION [dbo].[fnGetDateDiffAsYMD] (@FromDate AS DateTime,@ToDate AS DATETIME)

--Year,Date and Month section modified By ---------------Rajat Bhalla--------------------

RETURNS VARCHAR(30)

AS

BEGIN

DECLARE @date datetime,

@tmpdate datetime,

@years int,

@months int,

@days int,
@exp varchar(30),
@mm int,
@experiance datetime

if (datediff(dd,@FromDate ,@ToDate)< 0) or (@FromDate='') or (@ToDate is null)
select @exp ='Invalid joining date'
else
begin

select @experiance=Dateadd(yy,Datediff(yy,@FromDate,@ToDate),@fromDate)

select @years=Datediff(yy,@FromDate,@ToDate) - (CASE

WHEN @experiance > @ToDate THEN 1

ELSE 0

END)

select @months=Month(@ToDate - @experiance) -1

select @days = Day(@ToDate - @experiance) - 1

if @years<=0 and @months<=0 and @days<=0
set @exp = '0';

else if @years<=0
begin
if @months>0
begin
if @days>0
begin
if @months>1
begin
if @days>1

set @exp= CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Day'

end
else if @months=1
begin
if @days>1

set @exp= CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Day'

end

end

else if @days<=0
begin
if @months>1
set @exp= CAST(@months as varchar) + ' months'
else if @months=1
set @exp= CAST(@months as varchar) + ' month'
end
end

else if @months<=0

if @days>1
set @exp = CAST(@days as varchar) + ' Days'
else if @days=1
set @exp = CAST(@days as varchar) + ' Day'
end

else if @years>0 and @months>0 and @days>0
begin
if @years>1
begin
if @months>1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Day'
end

else if @months=1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Day'
end
end

else if @years=1

begin
if @months>1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' months ' +'and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' months ' +'and ' + CAST(@days as varchar) + ' Day'
end

else if @months=1
begin
if @days>1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' month ' +'and ' + CAST(@days as varchar) + ' Days'
else if @days=1
set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' month ' +'and ' + CAST(@days as varchar) + ' Day'
end
end

end

else if @years>0 and @days>0 and @months<=0
begin

if(@years>1)

begin
if(@days>1)
set @exp = CAST(@years as varchar) +' Years' +' and ' + CAST(@days as varchar) + ' Days'
else if(@days=1)
set @exp = CAST(@years as varchar) +' Years' +' and ' + CAST(@days as varchar) + ' Day'
end

else if(@years=1)

begin
if(@days>1)
set @exp = CAST(@years as varchar) +' Year' +' and ' + CAST(@days as varchar) + ' Days'
else if(@days=1)
set @exp = CAST(@years as varchar) +' Year' +' and ' + CAST(@days as varchar) + ' Day'
end

end

else if @years>0 and @days<=0 and @months<=0
begin
if @years>1

set @exp = CAST(@years as varchar) +' Years'
else if @years=1
set @exp = CAST(@years as varchar) +' Year'
end
else if @years>0 and @days<=0 and @months>0
begin
if @years>1
begin
if @months>1
set @exp = CAST(@years as varchar) +' Years' +' and ' + cast(@months as varchar) + ' months'
else if @months=1
set @exp = CAST(@years as varchar) +' Years' +' and ' + cast(@months as varchar) + ' month'
end

else if @years=1
begin
if @months>1
set @exp = CAST(@years as varchar) +' Year' +' and ' + cast(@months as varchar) + ' months'
else if @months=1
set @exp = CAST(@years as varchar) +' Year' +' and ' + cast(@months as varchar) + ' month'
end

end

end

return @exp

END

Post #1057924

gopak-378374

Posted Monday, October 29, 2012 8:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 12, 2012 8:25 AM
Points: 2, Visits: 25

Try This.

DECLARE @date1 DATETIME,
@date2 DATETIME,
@year INT,
@month INT,
@days INT,
@hours INT,
@min INT,
@sec INT

SELECT @date1 = '2012-10-29 18:34:31.013',
@date2 = '2013-12-29 20:54:41.056'

select @year = DATEDIFF(yy,@date1, @date2),
@date1 = DATEADD(yy,@year,@date1),
@month = DATEDIFF(MM,@date1, @date2),
@date1 = DATEADD(MM,@month,@date1),
@days = DATEDIFF(DD,@date1, @date2),
@date1 = DATEADD(dd,@days,@date1),
@hours = DATEDIFF(hh,@date1, @date2),
@date1 = DATEADD(hh,@hours,@date1),
@min = DATEDIFF(MI,@date1, @date2),
@date1 = DATEADD(mi,@min,@date1),
@sec = DATEDIFF(SS,@date1, @date2)
SELECT CASE WHEN @year = 0 THEN '' ELSE CAST (@year AS VARCHAR)+'Y ' END+
CASE WHEN @month = 0 THEN '' ELSE CAST (@month AS VARCHAR)+'M ' END+
CASE WHEN @days = 0 THEN '' ELSE CAST (@days AS VARCHAR)+'D ' END+
CASE WHEN @hours = 0 THEN '' ELSE CAST (@hours AS VARCHAR)+'H ' END+
CASE WHEN @min = 0 THEN '' ELSE CAST (@min AS VARCHAR)+'Mi ' END+
CASE WHEN @sec = 0 THEN '' ELSE CAST (@sec AS VARCHAR)+'Sec ' END

Post #1378272

bill 73333

Posted Monday, February 25, 2013 1:02 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 25, 2013 4:16 PM
Points: 1, Visits: 21

Getting the months and years between two dates using Datediff

There is a round up issue with the Datediff function.
When the reporting date month is the same as the target month you can get a round up
For example using a report date of 2012-05-06
And looking for the age of a person (years and months only)
This code looks like it should work:

declare @AsOnDate datetime
declare @mdob datetime
set @AsOnDate = '2012-05-06' --report date or from datetime function
set @mdob = '2011-05-01' --yyyy,mm,dd Birth Dates for examples
Select
"Years" = (datediff(month,@mdob,@AsOnDate)/12) , -- Integer Div to get years from total months
"Months" = (datediff(month,@mdob,@AsOnDate)% 12) -- Mod Div to get remaining months

However we get this results for a report date of '2012-05-06' and Birth Date of :
2011-05-01 we get 1 year 0 months - correct - report is 5 days after birthday
2011-05-06 we get 1 year 0 months - correct - report is on birthday
2011-05-26 we get 1 year 0 months - Wrong - report is 20 days before birthday should be 0 Years 11 months

This round up is very easy to miss!! Unsure

To correct this we need to check the dates and if the report month and target month are the same then we need to correct for the round up by reducing a month if the target day is greater than the report day (not a full month yet). The following code corrects for this.

declare @AsOnDate datetime
declare @mdob datetime
set @AsOnDate = '2012-05-06' --report date or from datetime function
set @mdob = '2011-05-07' --yyyy,mm,dd Birth Date for example
Select
"Years" = CASE
WHEN (DATEPART( DAY , @mdob)) > (DATEPART( DAY , @AsOnDate))
and (DATEPART( Month,@mdob)) = (DATEPART(Month, @AsOnDate))
THEN ((datediff(month,@mdob,@AsOnDate)-1)/12)
ELSE (datediff(month,@mdob,@AsOnDate)/12)
END,
"Months" = CASE
WHEN (DATEPART( DAY , @mdob)) > (DATEPART( DAY , @AsOnDate))
and (DATEPART( Month,@mdob)) = (DATEPART( Month,@AsOnDate))
THEN ((datediff(month,@mdob,@AsOnDate)-1)% 12)
ELSE (datediff(month,@mdob,@AsOnDate)% 12)
END

Results with correction:
2011-05-01 we get 1 year 0 months - correct - report is 5 days after birthday
2011-05-06 we get 1 year 0 months - correct - report is on birthday
2011-05-26 we get 0 years 11 months - correct - report is 20 days before birthday

Post #1423756

« Prev Topic | Next Topic »

Permissions