number of seconds to years-months-weeks-days-HH:MM-SS
Assuming you know @Seconds and @StartDateTime, something like the following could work:

 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] &lt; [StartMonth] THEN 1
                                             ELSE 0
                                           END AS [YearDiff] -- If EndMonth is less than StartMonth, subtract 1 Year from the naive differnce
               , CASE
                   WHEN [EndMonth] &gt;= [StartMonth] THEN [EndMonth] - [StartMonth] - CASE
                                                                                      WHEN [EndDay] &lt; [StartDay] THEN 1
                                                                                      ELSE 0
                                                                                    END -- If EndMonth &gt;= StartMonth -&gt; If EndDay is greater than StartDay, subtract 1 Month from the naive differnce
                   ELSE 12 - ( [StartMonth] - [EndMonth] ) -- If EndMonth &lt; 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] &gt;= [StartDay] THEN [EndDay] - [StartDay] -- If EndDay &gt;= StartMonth, then take a simple differnce
                   ELSE DATEDIFF(DD, DATEFROMPARTS([EndYear], [EndMonth] - 1, [StartDay]), [EndLastOfPreviousMonth]) + [EndDay] -- If EndDay &lt; 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];