TSQL - Converting HH:MM:SS to seconds

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 2000 Forums

Transact-SQL (2000)

TSQL - Converting HH:MM:SS to seconds

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2009-03-23 : 11:09:59

Hi,
I have a timestamp of 00:48:38 and I need to convert it to seconds. In seconds format it should calculate to 2918 seconds.

Anyone have any idea how to do this in tsql?

Thanks,
Ninel

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-23 : 11:21:05

In what datatype is your "timestamp" stored? If some flavor of varchar are there leading zeros for values < 10? (ie: '04:01:08')

EDIT:
assuming varchar with leading zeros


select ts
       ,convert(int, substring(ts, 1,2)) * 60 * 60
       + convert(int, substring(ts, 4,2)) * 60
       + convert(int, substring(ts, 7,2)) as [seconds]
from   (
       select '00:48:38' ts union all
       select '04:01:08'
       ) d

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-23 : 12:11:43

[code]
select
*,
DiffSeconds = datediff(ss,0,ts)
from
( -- Test data
select '00:48:38' ts union all
select '04:01:08'
) a

Results:

ts DiffSeconds
-------- -----------
00:48:38 2918
04:01:08 14468

(2 row(s) affected)
[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources