SQL Server : determining the Years, 两个日期之间的月、周和天

Question

在阅读本主题并被建议使用 DateDiff 之后。我写了一个没有提供我想要的答案的函数。客户现在想知道完成 list 需要多长时间。我有一个 CreationDate 和 CompletionDate。我需要知道这花了多少年、几个月、几周和几天。如果是 2 天，则没有年份的“2 天”。该函数减去年数，然后尝试检查月数，然后是周数，然后是天数。仅在可用时给出结果。看来 DateDiff 是问题...或者我是不理解 DateDiff 的问题。它甚至返回一个星期，日期相差 4 天，这是没有意义的。它应该返回两个日期内的周数，而不关心它何时开始。

这是代码

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

由于您使用的是函数，请考虑以下表值函数。易于独立使用或作为交叉应用包含在内。

高性能且准确，无需担心所有杂项日期计算。

示例

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')