Getting Time Remaining in SQL

SQL has types to deal with dates, including adding and subtracting dates; however, what if you need to show a formatted result of the remaining time from now to a specified period? Luckily, math is your friend in this instance, and we can take the resulting calculations, and format a string to send back in the result set.

CASE
          WHEN DATEDIFF(ss, GETDATE(), DATEADD(DAY, 2, t.DateCreated)) > 0
              THEN RIGHT('0' + CAST(DATEDIFF(ss, GETDATE(), DATEADD(DAY, 2, t.DateCreated)) / 3600 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST((DATEDIFF(ss, GETDATE(), DATEADD(DAY, 2, t.DateCreated)) / 60) % 60 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(DATEDIFF(ss, GETDATE(), DATEADD(DAY, 2, t.DateCreated)) % 60 AS VARCHAR), 2)
          ELSE '00:00:00'
      END AS [TimeRemaining]
      

The query uses a couple of tricks, and makes a few assumptions. First it assumes you want the time remaining in 00:00:00 format, so the end result is going to be a varchar. Second, we're checking against a 48 hour interval. Third, we don't want negative time, so if the difference of the dates is less than zero, we're just going to return 00:00:00.

Most of this is simple math. We're taking the difference between the two dates in seconds and dividing by 3600 to get the hours, then using the modulo operator in the next two statements as a way of acquiring the remainder values after division. This provides us with clean time parts. We cast each of those time parts as a varchar, and then pad them with zeroes to maintain the format that we want.