Skip to main content

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;