Converting Minutes To Time (hh:nn)

Question

Hi,

Running SQL Server 2008 R2.

I have an elapsed time column called [Elapsed_Time] with a data type of varchar. This is due to the values in this column being stored in 'hh:nn' format. I have a requirement to calculate the total elapsed time for a set of rows.

[CODE]
CREATE TABLE [dbo].[Test]
(
[Elapsed_Time] varchar NULL
)
ON [PRIMARY]
GO

INSERT INTO [dbo].[Test]
([Elapsed_Time])
VALUES
('08:00'),
('08:00'),
('08:00'),
('08:00'),
('04:00')
GO
[/CODE]

I have started by converting the 'hh:nn' values to minutes in my query. My thought was I could SUM the minutes then do the conversion back to 'hh:nn' format but I'm stumbling on the last part. Based on the above sample there is a total of 2160 minutes. I would like to express the output as 36:00. I should also note that the output could exceed 100 hours (100:00) so I'm not sure that fixed-length string conversion is the way to go.

Thanks in advance for any assistance!

Answer

If large intervals (such as '123:45') are possible for both of input and output values, then try manipulations like these:

select concat( format(sum(e) / 60, '00'), ':', format(sum(e) % 60, '00'))
FROM  Test
cross apply (values (charindex(':', Elapsed_Time))) I(i)
cross apply (values (left(Elapsed_Time, i - 1), substring(Elapsed_Time, i + 1, 50))) T(h,m)
cross apply (values (h*60+m)) E(e)

Answer

I posted an answer last night, but I deleted it, since the answer was off the mark, because I did not read the question well enough.

Let me first say that this is not good way of storing time. Preferably, you should use any of the built-in data types for date and time. Then again, SQL Server does not have an interval type, so there is no perfect solution. But I would prefer to store the elapsed time as an integer value with the number of minutes. Formatting can always be done later. With your current setup, there is a considerable risk that illegal values sneak into the table.

Anyway, here is a query. It first replaces the : with ., so the string can be split with the parsename function. Then it is straightforward to convert all to minutes and make the sum. The final SELECT performs the formatting (although, this may better be done in the presentation layer). I'm using the str function which produces a right-adjusted string of a numeric value. The second argument specifies the width we want. With replace, I replace the space with a 0. Note here that / is integer division, since both values are integer. % is the modulus operator.

; WITH first_replace AS (
   SELECT replace(Elapsed_Time, ':', '.') AS T
   FROM   Test
)
, parts AS (
   SELECT convert(int, parsename(T, 2)) AS hours,
          convert(int, parsename(T, 1)) AS minutes
   FROM   first_replace
) 
, dothesum AS (
   SELECT SUM(60*hours + minutes) AS total
   FROM   parts
)
SELECT str(total / 60, 5) + ':' + replace(str(total % 60, 2), ' ', '0')
FROM   dothesum
go
DROP TABLE Test

Answer

Hi @VDT-7677 ,

Please also refer to below and check whether it is helpful.

select rtrim(ltrim(cast([ETime]/60 as char(4))))+':'+ right('0'+rtrim(ltrim(cast([ETime]%60 as char(2)))),2) [Elapsed_Time] from   
(select sum(cast(left([Elapsed_Time],2) as int) *60 +cast(right([Elapsed_Time],2) as int)) [ETime] from [dbo].[Test]) a

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

Please see:
https://www.sqlteam.com/articles/working-with-time-spans-and-durations-in-sql-server

Converting Minutes To Time (hh:nn)

4 answers