問題描述

閱讀本主題并被建議使用 DateDiff 后.我寫了一個沒有提供我想要的答案的函數.客戶現在想知道完成清單需要多長時間.我有一個 CreationDate 和 CompletionDate.我需要知道花了多少年、月、周和天.如果是 2 天，則是沒有年份的2 天".該函數減去年數，然后嘗試檢查月數，然后是周數，然后是天數.如果可用，只給出結果.似乎 DateDiff 是問題所在……或者我是不理解 DateDiff 的問題.它甚至返回一個星期，因為日期相差 4 天，這是沒有意義的.它應該返回兩個日期內的周數，而不關心它何時開始.

After reading on this topic and being advised to use DateDiff. I wrote a function that doesn't provide the answer I want. The client wants to now how long it took to complete a checklist. I have a CreationDate and CompletionDate. I need to know how many years, months, weeks and days it took. If it is 2 days then '2 days' without the years. The function deducts the number of years and then attempt to check the number of months, then the number of weeks and then the number of days. Only results are given if available. It seems DateDiff is the problem... or I am the problem not understanding DateDiff. It even returns a week for a 4 day difference in dates which doesn't make sense. It should return the number of weeks within the two dates, not caring when it starts.

這是代碼

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; 

推薦答案

由于您正在使用函數，請考慮以下表值函數.易于使用的單機版或作為 CROSS APPLY 包含在內.

Since you are using a function, consider the following Table-Valued Function. Easy to use a stand-alone or included as a CROSS APPLY.

高性能且準確，無需擔心所有雜項日期計算.

Performant and Accurate without having to worry about all the misc date calculations.

示例

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

這篇關于SQL Server:確定兩個日期之間的年、月、周和日的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！