Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to get date difference in years month and date


how to get date difference in years month and date

Author
Message
kuppurajm
kuppurajm
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
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
VSSGeorge
VSSGeorge
Say Hey Kid
Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)

Group: General Forum Members
Points: 681 Visits: 1401
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'
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 7660
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
kuppurajm
kuppurajm
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 198
It give result like 0Y : 2M : 63D
I need the result like:2M:2days (2010-08-10,2010-10-12)
kuppurajm
kuppurajm
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 198
Thank you craig.
It may solve my issus
raj_yash22
raj_yash22
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
gopak-378374
gopak-378374
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 31
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
bill 73333
bill 73333
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 33
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search