Converting total seconds to hh:mm:ss

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

SQL Server Development (2000)

Converting total seconds to hh:mm:ss

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-12 : 07:14:16

Michael writes "I have created a query where I return the total call time in seconds. It returns a number like 64597. I need to convert this number to hh:mm:ss. Right now the line to get those seconds looks like this

SUM(DATEDIFF(second, CALLSTARTTIME, CALLENDTIME)) AS total_call_time

I need to make this sum output in hh:mm:ss format."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-12 : 08:28:54

[code]declare
@sec int

select @sec = 64597

select right('0' + rtrim(convert(char(2), @sec / (60 * 60))), 2) + ':' +
right('0' + rtrim(convert(char(2), (@sec / 60) % 60)), 2) + ':' +
right('0' + rtrim(convert(char(2), @sec % 60)),2)
[/code]

-----------------
[KH]

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-12 : 08:32:15

lalalalalala


DECLARE 
	@CALLSTARTTIME DATETIME,
	@CALLENDTIME DATETIME

SELECT
	@CALLSTARTTIME = '12/24/1995 04:14:00.000',
	@CALLENDTIME = '12/24/1995 04:25:13.000'

SELECT DATEDIFF(second, @CALLSTARTTIME, @CALLENDTIME) AS total_call_time

SELECT CONVERT(VARCHAR,GETDATE(),23) --Casts date without time, which starts at beginning of day.
SELECT CONVERT(VARCHAR,GETDATE(),24) --Returns just time.

--Get time with date.
SELECT DATEADD(SS,DATEDIFF(second, @CALLSTARTTIME, @CALLENDTIME),CONVERT(VARCHAR,GETDATE(),23))

--Get time without date.
SELECT CONVERT(VARCHAR,DATEADD(SS,DATEDIFF(second, @CALLSTARTTIME, @CALLENDTIME),CONVERT(VARCHAR,GETDATE(),23)),24)

For more on datetime functions, check out: http://weblogs.sqlteam.com/derrickl/archive/2005/01/08/3959.aspx

I'm sure there's an easier way to do this, but hey.....who needs easy. This was quick to throw together.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-12-12 : 14:33:48

Or:
SELECT CONVERT(varchar, DATEADD(s, 64597, 0), 108)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-12 : 17:03:28

I'm sure there's an easier way to do this, but hey.....who needs easy. This was quick to throw together.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-12-12 : 18:30:01

quote:
Originally posted by derrickleggett

I'm sure there's an easier way to do this, but hey.....who needs easy.

Let's say you want 100s or 1000s of hours...

declare @i int
--set @i = 3621
set @i = 363719 -- more than 100 hours
select replace(str(@i/3600,len(ltrim(@i/3600))+abs(sign(@i/359999)-1)) + ':' + str((@i/60)%60,2) + ':' + str(@i%60,2),' ','0')

--------------
101:01:59

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-12 : 18:44:41

I'm sure there's an easier way to do this, but hey.....who needs easy. This was quick to throw together.

You know......I'm getting a LOT of mileage out of this.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-12 : 18:47:56

you are right. There’s more than one way to skin a cat

-----------------
[KH]

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-12-12 : 18:59:41

And the fellows doing the PRESENTATION TIER will truncate it to char(8)...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-13 : 00:36:19