Get accurate duration time between two dates as year, month, day format in SQL Server.
if exists (select * from sys.objects where object_id = OBJECT_ID(N'[dbo].[GetDurationTime]')
and type in (N'FN', N'IF', N'TF', N'FS', N'FT')
)
begin
drop function dbo.GetDurationTime
end;
go
-- ============================================================================
-- Usage:
-- declare @startdate datetime = getdate();
-- declare @enddate datetime = dateadd(dd, -520, getdate());
-- select dbo.GetDurationTime(@startdate, @enddate); -- 1 Years :5 Months :3 Days
-- ============================================================================
create function dbo.GetDurationTime(
@StartDateTime datetime,
@EndDateTime datetime
)
returns nvarchar(40)
as
begin
if @StartDateTime > @EndDateTime
begin
declare @Tempvar datetime = @StartDateTime;
set @StartDateTime = @EndDateTime;
set @EndDateTime = @Tempvar;
end;
return (select cast(Years as varchar(4))+' years :'+cast(Months as varchar(2))+' months :'+cast(Days as varchar(2))+' days'
from (-- f
select Years, Months, Days = datediff(DAY, dateadd(MONTH, Months, dateadd(YEAR, Years, @StartDateTime)), @EndDateTime)
- case
when dateadd(DAY, datediff(DAY, dateadd(MONTH, Months, dateadd(YEAR, Years, @StartDateTime)), @EndDateTime), dateadd(MONTH, Months, dateadd(YEAR, Years, @StartDateTime))) > @EndDateTime
then 1
else 0
end
from ( -- e
select Years, Months = datediff(MONTH, dateadd(YEAR, Years, @StartDateTime), @EndDateTime)
- case
when dateadd(MONTH, datediff(MONTH, dateadd(YEAR, Years, @StartDateTime), @EndDateTime), dateadd(YEAR, Years, @StartDateTime)) > @EndDateTime
then 1
else 0
end
from ( -- d
select Years = datediff(YEAR, @StartDateTime, @EndDateTime)
- case
when dateadd(YEAR, datediff(YEAR, @StartDateTime, @EndDateTime), @StartDateTime) > @EndDateTime
then 1
else 0
end
) as d
) as e)
as f);
end;