question

VDT-7677 avatar image
0 Votes"
VDT-7677 asked MelissaMa-msft commented

Converting Minutes To Time (hh:nn)

Hi,

Running SQL Server 2008 R2.

I have an elapsed time column called [Elapsed_Time] with a data type of [varchar](50). 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](50) 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!

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @VDT-7677,

Could you please validate all the answers so far and provide any update?

If all are not working, please provide more sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered ErlandSommarskog commented

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)

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Alas, that is not going to work out, since VDT is on SQL 2008 R2, so format and concat are not available.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog edited

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.