Convert hh:mm:ss in a Column Stored as Text

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Convert hh:mm:ss in a Column Stored as Text

Author

Topic

secuchalan
Starting Member

19 Posts

Posted - 2011-09-14 : 05:57:47

Our application has a status history table that calculates the total time spent by change of status.

I am exporting the result to Excel and would like to get the total time spent in seconds by date (whether grouped by day, month, year) through a pivot table. This will be straightforward if the value in the TotalTime column is not formatted as text (var char). Is there a way to convert these column through SQL so I get an integer value?

The goal is to be able to convert the TotalTime column (currently as text entered as hh:mm:ss) to seconds.

Here is an example:


select ticketid, status, changedate, totaltime
from tkstatus
where ticketid = '1019'
and siteid = 'ICT'
order by changedate


ticketid             status               changedate              totaltime
-------------------- -------------------- ----------------------- ----------------------------------------------------------------------------------------------------
1019                 NEW                  2008-12-01 13:33:01.390 00:09:13
1019                 QUEUED               2008-12-01 13:42:15.313 00:00:04
1019                 INPROG               2008-12-01 13:42:19.880 00:39:31
1019                 RESOLVED             2008-12-01 14:21:51.407 45:16:30
1019                 CLOSED               2008-12-03 11:38:21.643 NULL

(5 row(s) affected)

Desired result:


ticketid	status		changedate			totaltime	TotTimeInSeconds
1019		NEW		2008-12-01 13:33:01.390		00:09:13	553
1019		QUEUED		2008-12-01 13:42:15.313		00:00:04	4
1019		INPROG		2008-12-01 13:42:19.880		00:39:31	2371
1019		RESOLVED	2008-12-01 14:21:51.407		45:16:30	76590
1019		CLOSED		2008-12-03 11:38:21.643		NULL		0

Thank you in advance.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-09-14 : 06:29:03

SELECT ticketid, status, changedate, totaltime,
3600*LEFT(totaltime,2)+60*SUBSTRING(totaltime,4,2)+1*RIGHT(totaltime,2) AS totaltimeInSeconds
FROM tkstatus
WHERE ticketid = '1019'
AND siteid = 'ICT'
ORDER BY changedate

--------------------------
http://connectsql.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-09-14 : 07:33:11

SELECT ticketid, status, changedate, totaltime,
DATEDIFF(ss,0,TotalTime)
FROM tkstatus
WHERE ticketid = '1019'
AND siteid = 'ICT'
ORDER BY changedate

Jim

Everyday I learn something that somebody else already knew

secuchalan
Starting Member

19 Posts

Posted - 2011-09-14 : 13:40:30

quote:
Originally posted by lionofdezert

SELECT ticketid, status, changedate, totaltime,
3600*LEFT(totaltime,2)+60*SUBSTRING(totaltime,4,2)+1*RIGHT(totaltime,2) AS totaltimeInSeconds
FROM tkstatus
WHERE ticketid = '1019'
AND siteid = 'ICT'
ORDER BY changedate

--------------------------
http://connectsql.com/

This worked, thank you lionofdezert.

However, when I convert some records with hours that are more than 2 numbers it errors out.

For instance if the totaltime is 6898:48:50 it will calculate it up to the minutes part, but errors out on calculating 6898.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-14 : 14:11:42

Is it too late to change how you store your data? It would seem to make more sense to store it as a DATETIME offset rather than a string, especially if you have values of almost 7000 hours. Then all this conversation nonsense becomes moot.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-14 : 14:18:26

Maybe this will help?

DECLARE @Foo TABLE (totaltime varchar(15))

INSERT @Foo (totaltime)
VALUES
('45:16:30'),
('00:09:13'),
(NULL),
('6898:48:50')


SELECT 
    (CAST(PARSENAME(REPLACE(totaltime, ':', '.'), 3) AS INT) * 3600)
    + (CAST(PARSENAME(REPLACE(totaltime, ':', '.'), 2) AS INT) * 60)
    + (CAST(PARSENAME(REPLACE(totaltime, ':', '.'), 1) AS INT))
FROM @Foo

EDIT: Fixing my cut-n-paste error. :)

secuchalan
Starting Member

19 Posts

Posted - 2011-09-14 : 14:35:11

quote:
Originally posted by Lamprey

Is it too late to change how you store your data? It would seem to make more sense to store it as a DATETIME offset rather than a string, especially if you have values of almost 7000 hours. Then all this conversation nonsense becomes moot.

Yes that is my wishful thinking and would make my life a lot more easier but it is too late. The org have been using this appplication for years now. I'm just analyzing the data.

secuchalan
Starting Member

19 Posts

Posted - 2011-09-14 : 15:11:26

quote:

Originally posted by secuchalan

quote:

Originally posted by Lamprey

Maybe this will help?

DECLARE @Foo TABLE (totaltime varchar(15))

INSERT @Foo (totaltime)
VALUES
('45:16:30'),
('00:09:13'),
(NULL),
('6898:48:50')


SELECT 
    (CAST(PARSENAME(REPLACE(totaltime, ':', '.'), 3) AS INT) * 3600)
    + (CAST(PARSENAME(REPLACE(totaltime, ':', '.'), 2) AS INT) * 60)
    + (CAST(PARSENAME(REPLACE(totaltime, ':', '.'), 1) AS INT))
FROM @Foo

This worked! Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources