Get the difference between two dates in years,months and days as a string from a SQL Server

Just use the function pass 2 date parameter & get your expected Year/Month/Day
SELECT  [dbo].[fnGetDateDiffAsYMD] ('01/01/2012','02/02/2014')as Duration  





 set ANSI_NULLS ON  
 set QUOTED_IDENTIFIER ON  
 go  
 --SELECT [dbo].[fnGetDateDiffAsYMD] ('01/01/2012','02/02/2013')   
 ALTER FUNCTION [dbo].[fnGetDateDiffAsYMD] (@FromDate AS DateTime,@ToDate AS DATETIME)  
 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  

you can also use

 SELECT DATEDIFF(DAY, GETDATE(), GETDATE() + 1) AS DayDiff  
 SELECT DATEDIFF(MINUTE, GETDATE(), GETDATE() + 1) AS MinuteDiff  
 SELECT DATEDIFF(SECOND, GETDATE(), GETDATE() + 1) AS SecondDiff  
 SELECT DATEDIFF(WEEK, GETDATE(), GETDATE() + 1) AS WeekDiff  
 SELECT DATEDIFF(HOUR, GETDATE(), GETDATE() + 1) AS HourDiff  

Popular posts from this blog

WPF Crystal Report Viewer Using SAP

mvc razor textboxfor change event change another textboxfor value

ASP.NET MVC razor SAP Crystal report