秒數到年-月-週-日-HH:MM-SS

我一直在執行它以從幾秒鐘內獲得 HH:MM:SS：

DECLARE @s INT
SELECT
   @s = 23251
SELECT
   @s
 , CONVERT(TIME, DATEADD(SECOND, @s, 0));

當我想添加days到等式時，我執行以下查詢：

DECLARE @seconds AS int = 232511;
SELECT
   CONVERT(varchar, (@seconds / 86400))                --Days
   + ':' +
   CONVERT(varchar, DATEADD(ss, @seconds, 0), 108);    --Hours, Minutes, Seconds

如果我也想要 , 和 的數量weeks怎麼months辦years？

我將如何解決這個問題？

我認為確切的計算需要參考when發生的秒數。

例如，假設我對 sql server 作業執行了多長時間感興趣。我的複制類別中的作業已經執行了多年，並且起點是已知的。

如果未指定，我們可以假設秒數達到now.

假設您知道@Seconds和@StartDateTime，類似以下的內容可能會起作用：

DECLARE @Seconds INT = 50000000;
DECLARE @StartDateTime DATETIME2(7) = GETDATE();

-- Get the EndDateTime
DECLARE @EndDateTime DATETIME2(7) = DATEADD([SECOND], @Seconds, @StartDateTime);

-- Make a one-row CTE of useful info for logic and math later
WITH Calendar_CTE
     AS (SELECT @StartDateTime AS [StartDateTime]
              , CONVERT(DATE, @StartDateTime) AS [StartDate]
              , DATEPART(YY, @StartDateTime) AS [StartYear]
              , DATEPART(MM, @StartDateTime) AS [StartMonth]
              , DATEPART(DD, @StartDateTime) AS [StartDay]
              , @EndDateTime AS [EndDateTime]
              , CONVERT(DATE, @EndDateTime) AS [EndDate]
              , DATEPART(YY, @EndDateTime) AS [EndYear]
              , DATEPART(MM, @EndDateTime) AS [EndMonth]
              , DATEPART(DD, @EndDateTime) AS [EndDay]
              , DATEADD(DD, -1, DATEFROMPARTS(DATEPART(YY, @EndDateTime), DATEPART(MM, @EndDateTime), 1)) AS [EndLastOfPreviousMonth]),
     -- Another one-row CTE, this time with each DATEPART's differences
     Differences
     AS (SELECT [EndYear] - [StartYear] - CASE
                                            WHEN [EndMonth] < [StartMonth] THEN 1
                                            ELSE 0
                                          END AS [YearDiff] -- If EndMonth is less than StartMonth, subtract 1 Year from the naive differnce
              , CASE
                  WHEN [EndMonth] >= [StartMonth] THEN [EndMonth] - [StartMonth] - CASE
                                                                                     WHEN [EndDay] < [StartDay] THEN 1
                                                                                     ELSE 0
                                                                                   END -- If EndMonth >= StartMonth -> If EndDay is greater than StartDay, subtract 1 Month from the naive differnce
                  ELSE 12 - ( [StartMonth] - [EndMonth] ) -- If EndMonth < StartMonth, subtract the difference from 12
                END AS [MonthDiff]                    -- e.g. this May to next March: 12 - (5 - 3) = 12 - 2 = 10 months
              , CASE
                  WHEN [EndDay] >= [StartDay] THEN [EndDay] - [StartDay] -- If EndDay >= StartMonth, then take a simple differnce
                  ELSE DATEDIFF(DD, DATEFROMPARTS([EndYear], [EndMonth] - 1, [StartDay]), [EndLastOfPreviousMonth]) + [EndDay] -- If EndDay < StartDay, find the remaining difference to the previous end-of-month, then add the rest of the days to the EndDate (this accounts for differnt length months)
                END AS [DayDiff]                                                                                   -- e.g. Jan 30th to Feb 2nd: (Jan 31st - Jan 30th) + 2 = 1 + 2 = 3 days
              , @Seconds / 60 / 60 % 24 AS [HourDiff]                                                              -- e.g. Jun 29th to Jul 2nd: (Jun 29th - Jun 30th) + 2 = 1 + 2 = 3 days 
              , @Seconds / 60 % 60 AS [MinuteDiff]
              , @Seconds % 60 AS [SecondDiff]
         FROM [Calendar_CTE])

    -- Put it all together
     SELECT CONCAT([YearDiff], ' Year(s), ', [MonthDiff], ' Month(s), ', [DayDiff] / 7, ' Week(s), ', [DayDiff] % 7, ' Day(s), ', [HourDiff], ' Hour(s), ', [MinuteDiff], ' Minute(s), ', [SecondDiff], ' Second(s)') AS [TimeElapsed]
     FROM [Differences];

引用自：https://dba.stackexchange.com/questions/150291