Sql-server – number of seconds to years-months-weeks-days-HH:MM-SS

date formatdate mathdatetimesql serversql server 2014

I am running this all the time to get HH:MM:SS from a number of seconds:

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

enter image description here

when I want to add days to the equation I run the following query:

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

enter image description here

what if I wanted as well the number of weeks, months, and years?

How would I go around that?

I think the exact calculation would need to have a reference to when the number of seconds happened.

Let's say for instance I am interested in how long a sql server job has been running for. I have jobs within the replication category that have been running for years, and the starting point is known.

When not specified we could assume the number of seconds are up to now.

Best Answer

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] < [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];
Related Question