how to get date difference in years month and date

 Author Message kuppurajm SSC Rookie 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 Say Hey Kid Group: General Forum Members Points: 681 Visits: 1401 Hi..Try thisselect 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 SSCertifiable Group: General Forum Members Points: 5695 Visits: 7660 Junglee_George (1/5/2011)Hi..Try thisselect 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 DATETIMESELECT @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 FarrellNever 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 NetiquetteFor index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA kuppurajm SSC Rookie Group: General Forum Members Points: 49 Visits: 198 It give result like 0Y : 2M : 63DI need the result like:2M:2days (2010-08-10,2010-10-12) kuppurajm SSC Rookie Group: General Forum Members Points: 49 Visits: 198 Thank you craig.It may solve my issus raj_yash22 Forum Newbie Group: General Forum Members Points: 2 Visits: 2 try this--By Rajat BhallaALTER 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'elsebeginselect @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) -1select @days = Day(@ToDate - @experiance) - 1if @years<=0 and @months<=0 and @days<=0set @exp = '0';else if @years<=0 beginif @months>0beginif @days>0 beginif @months>1beginif @days>1set @exp= CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Days'else if @days=1set @exp= CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Day'endelse if @months=1beginif @days>1set @exp= CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Days'else if @days=1set @exp= CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Day'endendelse if @days<=0beginif @months>1set @exp= CAST(@months as varchar) + ' months' else if @months=1set @exp= CAST(@months as varchar) + ' month' endendelse if @months<=0if @days>1set @exp = CAST(@days as varchar) + ' Days'else if @days=1set @exp = CAST(@days as varchar) + ' Day'endelse if @years>0 and @months>0 and @days>0beginif @years>1beginif @months>1beginif @days>1set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Days'else if @days=1set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' months' +' and ' + CAST(@days as varchar) + ' Day'endelse if @months=1beginif @days>1set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Days'else if @days=1set @exp= CAST(@years as varchar) +' Years' + ', '+ CAST(@months as varchar) + ' month' +' and ' + CAST(@days as varchar) + ' Day'endendelse if @years=1beginif @months>1beginif @days>1set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' months ' +'and ' + CAST(@days as varchar) + ' Days'else if @days=1set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' months ' +'and ' + CAST(@days as varchar) + ' Day'endelse if @months=1beginif @days>1set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' month ' +'and ' + CAST(@days as varchar) + ' Days'else if @days=1set @exp= CAST(@years as varchar) +' Year' + ', '+ CAST(@months as varchar) + ' month ' +'and ' + CAST(@days as varchar) + ' Day'endendendelse if @years>0 and @days>0 and @months<=0beginif(@years>1)beginif(@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'endelse if(@years=1)beginif(@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'endendelse if @years>0 and @days<=0 and @months<=0beginif @years>1set @exp = CAST(@years as varchar) +' Years' else if @years=1set @exp = CAST(@years as varchar) +' Year' endelse if @years>0 and @days<=0 and @months>0beginif @years>1beginif @months>1set @exp = CAST(@years as varchar) +' Years' +' and ' + cast(@months as varchar) + ' months'else if @months=1set @exp = CAST(@years as varchar) +' Years' +' and ' + cast(@months as varchar) + ' month'endelse if @years=1beginif @months>1set @exp = CAST(@years as varchar) +' Year' +' and ' + cast(@months as varchar) + ' months'else if @months=1set @exp = CAST(@years as varchar) +' Year' +' and ' + cast(@months as varchar) + ' month'endendend return @exp END gopak-378374 Forum Newbie 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   INTSELECT   @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 Forum Newbie Group: General Forum Members Points: 1 Visits: 33 Getting the months and years between two dates using DatediffThere 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 upFor example using a report date of 2012-05-06And looking for the age of a person (years and months only)This code looks like it should work:declare @AsOnDate datetimedeclare @mdob datetimeset @AsOnDate = '2012-05-06' --report date or from datetime functionset @mdob = '2011-05-01' --yyyy,mm,dd Birth Dates for examplesSelect "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 birthday2011-05-26 we get 1 year 0 months - Wrong - report is 20 days before birthday should be 0 Years 11 monthsThis 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 datetimedeclare @mdob datetimeset @AsOnDate = '2012-05-06' --report date or from datetime functionset @mdob = '2011-05-07' --yyyy,mm,dd Birth Date for exampleSelect "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 birthday2011-05-26 we get 0 years 11 months - correct - report is 20 days before birthday