Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to get date difference in years month and date Expand / Collapse
Author
Message
Posted Wednesday, January 05, 2011 1:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, January 05, 2011 2:12 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 12:52 AM
Points: 588, Visits: 1,004
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
Posted Wednesday, January 05, 2011 2:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:16 PM
Points: 5,986, Visits: 6,931
Junglee_George (1/5/2011)
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'



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
Posted Wednesday, January 05, 2011 2:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Wednesday, January 05, 2011 3:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Thursday, February 03, 2011 3:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, October 29, 2012 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, February 25, 2013 1:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 11:28 AM
Points: 1, Visits: 32
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!!

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 »

Add to briefcase

Permissions Expand / Collapse