Calculer la durée entre deux dates (le mois et l'année seront considérés)

Essaye celui-là:

 Declare @SDate DateTime ='06-Jan-2009' Declare @EDate DateTime ='12-Oct-2009' select case when DateDiff(M,@sDate,@EDate) <=12 then DateDiff(M,@sDate,@EDate) else Round( ( Convert(Decimal(18,0) ,DateDiff(M,@sDate,@EDate)/12.0)),0) end 

 declare @Birthdate datetime declare @AsOnDate datetime declare @years int declare @months int declare @days int declare @hours int declare @minutes int --NOTE: date of birth must be smaller than As on date, --else it could produce wrong results set @Birthdate = '1989-11-30 9:27 pm' --birthdate set @AsOnDate = Getdate() --current datetime --calculate years select @years = datediff(year,@Birthdate,@AsOnDate) --calculate months if it's value is negative then it --indicates after __ months; __ years will be complete --To resolve this, we have taken a flag @MonthOverflow... declare @monthOverflow int select @monthOverflow = case when datediff(month,@Birthdate,@AsOnDate) - ( datediff(year,@Birthdate,@AsOnDate) * 12) <0 then -1 else 1 end --decrease year by 1 if months are Overflowed select @Years = case when @monthOverflow < 0 then @years-1 else @years end select @months = datediff(month,@Birthdate,@AsOnDate) - (@years * 12) --as we do for month overflow criteria for days and hours --& minutes logic will followed same way declare @LastdayOfMonth int select @LastdayOfMonth = datepart(d,DATEADD (s,-1,DATEADD(mm, DATEDIFF(m,0,@AsOnDate)+1,0))) select @days = case when @monthOverflow<0 and DAY(@Birthdate)> DAY(@AsOnDate) then @LastdayOfMonth + (datepart(d,@AsOnDate) - datepart(d,@Birthdate) ) - 1 else datepart(d,@AsOnDate) - datepart(d,@Birthdate) end declare @hoursOverflow int select @hoursOverflow = case when datepart(hh,@AsOnDate) - datepart(hh,@Birthdate) <0 then -1 else 1 end select @hours = case when @hoursOverflow<0 then 24 + datepart(hh,@AsOnDate) - datepart(hh,@Birthdate) else datepart(hh,@AsOnDate) - datepart(hh,@Birthdate) end declare @minutesOverflow int select @minutesOverflow = case when datepart(mi,@AsOnDate) - datepart(mi,@Birthdate) <0 then -1 else 1 end select @minutes = case when @hoursOverflow<0 then 60 - (datepart(mi,@AsOnDate) - datepart(mi,@Birthdate)) else abs(datepart (mi,@AsOnDate) - datepart(mi,@Birthdate)) end select @Months=case when @days < 0 or DAY(@Birthdate)> DAY(@AsOnDate) then @Months-1 else @Months end Declare @lastdayAsOnDate int; set @lastdayAsOnDate = datepart(d,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@AsOnDate),0))); Declare @lastdayBirthdate int; set @lastdayBirthdate = datepart(d,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Birthdate)+1,0))); if (@Days < 0) ( select @Days = case when( @lastdayBirthdate > @lastdayAsOnDate) then @lastdayBirthdate + @Days else @lastdayAsOnDate + @Days end ) print convert(varchar,@years) + ' year(s), ' + convert(varchar,@months) + ' month(s), ' + convert(varchar,@days) + ' day(s), ' + convert(varchar,@hours) + ':' + convert(varchar,@minutes) + ' hour(s)' 

S'il vous plaît essayé avec la requête ci-dessous

 Declare @SDate DateTime ='01-Jan-2009' Declare @EDate DateTime ='12-Oct-2009' SET @EDate = DATEADD(M, 1, @EDate) select DateDiff(M,@sDate,@EDate) 

 select Round( ( Convert(Decimal(18,2),DateDiff(M,'06-Jan-2009','12-Dec-2010')/12.0)),0) as Years 

Je pense que ce serait un travail pour moi:

 DECLARE @start DATETIME='01-Jan-2008' DECLARE @end DATETIME='10-Nov-2011' DECLARE @duration INT --SELECT @start = '2011-10-10', @end = '2013-10-10' --select DATEPART(dd,@start) SET @Start = Dateadd(DAY, -( Datepart(DD, @Start) - 1 ), @Start) SET @end = Dateadd(DAY, -( Datepart(day, @end) - 1 ), @end) SET @end = Dateadd(day, Day(Dateadd(mm, Datediff(mm, -1, @end), -1)), @end) SELECT @duration = Datediff(mm, @Start, @end) SELECT CASE WHEN @duration = 0 THEN NULL WHEN @duration%12 = 0 THEN( CONVERT(NVARCHAR, @duration / 12) + ' Year ' ) WHEN @duration > 12 THEN( CONVERT(NVARCHAR, @duration / 12) + ' Year ' + CONVERT(NVARCHAR, @duration % 12) + ' Month ' ) ELSE (SELECT CONVERT(NVARCHAR, @duration % 12) + ' Month') END 

 declare @Birthdate datetime declare @AsOnDate datetime declare @years varchar(4) declare @months varchar(3) declare @days varchar(3) declare @hours varchar(3) declare @minutes varchar(2) set @Birthdate = '1989-11-30 9:27 AM' --birthdate set @AsOnDate = getdate() --current datetime select @years = datediff(year,@Birthdate,@AsOnDate) select @months = datediff(month,@Birthdate,@AsOnDate) - ( datediff(year,@Birthdate,@AsOnDate) * 12) select @days = datepart(d,@AsOnDate) - datepart(d,@Birthdate) select @hours = datepart(hh,@AsOnDate) - datepart(hh,@Birthdate) select @minutes = abs(datepart(mi,@AsOnDate) - datepart(mi,@Birthdate)) print @years + ' year(s), ' + @months + ' month(s), ' + @days + ' day(s), ' + @hours + ':' + @minutes + ' hour(s)' 

OUT PUT: