SQL Server: Defining Years, Months, Weeks, and Days Between Two Dates

Question

After reading this topic and advise to use DateDiff. I wrote a function that does not provide the answer I want. The client wants how long it took to fill out the checklist. I have CreationDateand CompletionDate. I need to know how many years, months, weeks and days have passed. If it is 2 days, then "2 days" without years. The function subtracts the number of years, and then tries to check the number of months, then the number of weeks, and then the number of days. If available, only results are available. There seems to be DateDiffa problem ... or I do not understand the understanding DateDiff. He even returns a week for a 4-day difference in dates, which does not make sense. He should return the number of weeks over two dates, and not care about when it starts.

This is the code

ALTER FUNCTION [dbo].[DateRangeText]
    (@FromDate DATETIME, @ToDate DATETIME)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Result AS VARCHAR(MAX);
    SET @Result = '';

    DECLARE @TmpS AS VARCHAR(MAX);
    SET @TmpS = '';

    DECLARE @Years AS INT;
    SET @Years = DATEDIFF(year, @FromDate, @ToDate);
    IF (@Years > 0)
    BEGIN
        IF (@Years = 1)
            SET @TmpS = ' Year ';
        ELSE
            SET @TmpS = ' Years ';

        SET @Result = @Result + CAST(@Years AS VARCHAR) + @TmpS;
        SET @ToDate = DATEADD(YEAR, -1 * @Years, @ToDate);
    END;

    DECLARE @Months AS INT;
    SET @Months = DATEDIFF(month, @FromDate, @ToDate);
    IF (@Months > 0)
    BEGIN
        IF (@Months = 1)
            SET @TmpS = ' Month ';
        ELSE
            SET @TmpS = ' Months ';

        SET @Result = @Result + CAST(@Months AS VARCHAR) + @TmpS;
        SET @ToDate = DATEADD(MONTH, -1 * @Months, @ToDate);
    END;

    DECLARE @Weeks AS INT;
    SET @Weeks = DATEDIFF(week, @FromDate, @ToDate);
    IF (@Weeks > 0)
    BEGIN
        IF (@Weeks = 1)
            SET @TmpS = ' Week ';
        ELSE
            SET @TmpS = ' Weeks ';

        SET @Result = @Result + CAST(@Weeks AS VARCHAR) + @TmpS;
        SET @ToDate = DATEADD(WEEK, -1 * @Weeks, @ToDate);
    END;

    DECLARE @Days AS INT;
    SET @Days = DATEDIFF(day, @FromDate, @ToDate);
    IF (@Days > 0)
    BEGIN
        IF (@Days = 1)
            SET @TmpS = ' Day ';
        ELSE
            SET @TmpS = ' Days ';

        SET @Result = @Result + CAST(@Days AS VARCHAR) + @TmpS;
        SET @ToDate = DATEADD(WEEK, -1 * @Days, @ToDate);
    END;

    IF (@Result = '')
        SET @Result = 'Same day';

    RETURN Rtrim(COALESCE(@Result,''));
END; 

Answer 1

, . CROSS APPLY.

, .

Select * from [dbo].[tvf-Date-Elapsed] ('1991-09-12 21:00:00.000',GetDate())

Years   Months  Days    Hours   Minutes Seconds
26      7       5       13      47      11

TVF,

CREATE FUNCTION [dbo].[tvf-Date-Elapsed] (@D1 DateTime,@D2 DateTime)
Returns Table
Return (
    with cteBN(N)   as (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cteRN(R)   as (Select Row_Number() Over (Order By (Select NULL))-1 From cteBN a,cteBN b,cteBN c),
         cteYY(N,D) as (Select Max(R),Max(DateAdd(YY,R,@D1))From cteRN R Where DateAdd(YY,R,@D1)<=@D2),
         cteMM(N,D) as (Select Max(R),Max(DateAdd(MM,R,D))  From (Select Top 12 R From cteRN Order By 1) R, cteYY P Where DateAdd(MM,R,D)<=@D2),
         cteDD(N,D) as (Select Max(R),Max(DateAdd(DD,R,D))  From (Select Top 31 R From cteRN Order By 1) R, cteMM P Where DateAdd(DD,R,D)<=@D2),
         cteHH(N,D) as (Select Max(R),Max(DateAdd(HH,R,D))  From (Select Top 24 R From cteRN Order By 1) R, cteDD P Where DateAdd(HH,R,D)<=@D2),
         cteMI(N,D) as (Select Max(R),Max(DateAdd(MI,R,D))  From (Select Top 60 R From cteRN Order By 1) R, cteHH P Where DateAdd(MI,R,D)<=@D2),
         cteSS(N,D) as (Select Max(R),Max(DateAdd(SS,R,D))  From (Select Top 60 R From cteRN Order By 1) R, cteMI P Where DateAdd(SS,R,D)<=@D2)

    Select [Years]   = cteYY.N
          ,[Months]  = cteMM.N
          ,[Days]    = cteDD.N
          ,[Hours]   = cteHH.N
          ,[Minutes] = cteMI.N
          ,[Seconds] = cteSS.N
          --,[Elapsed] = Format(cteYY.N,'0000')+':'+Format(cteMM.N,'00')+':'+Format(cteDD.N,'00')+' '+Format(cteHH.N,'00')+':'+Format(cteMI.N,'00')+':'+Format(cteSS.N,'00')
     From  cteYY,cteMM,cteDD,cteHH,cteMI,cteSS
)
--Max 1000 years
--Select * from [dbo].[tvf-Date-Elapsed] ('1991-09-12 21:00:00.000',GetDate())
--Select * from [dbo].[tvf-Date-Elapsed] ('2017-01-01 20:30:15','2018-02-05 22:58:35')

Answer 2

, . , , :

DECLARE @FromDate DateTime
DECLARE @ToDate DateTime
DECLARE @years INT
DECLARE @months INT
DECLARE @days INT

-- just some sample dates for testing   
Set @FromDate = '01-01-2014'
Set @ToDate = GetDate()

SET @years = DATEDIFF(mm, @FromDate, @ToDate)/12
SET @months = DATEDIFF(mm, @FromDate, @ToDate)%12 - 1
SET @days = ABS(DATEDIFF(dd, DATEADD(mm,@months , DATEADD(yy, @years, @FromDate)), @ToDate))

DECLARE @YearsStr VarChar(20)
DECLARE @MonthsStr VarChar(20)
DECLARE @DaysStr VarChar(20)

SET @YearsStr = Case When @years > 0 Then Convert(varchar(10),@years) + ' Years, ' Else '' End
SET @MonthsStr = Case When @months > 0 Then Convert(varchar(10),@months) + ' Months, ' Else '' End
SET @DaysStr = Convert(varchar(10),@days) + ' Days '

SELECT @YearsStr + @MonthsStr + @DaysStr