Convert Numeric value To HH:MM:SS format

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)

Convert Numeric value To HH:MM:SS format

Author

Topic

Splics
Starting Member

39 Posts

Posted - 2006-03-17 : 13:55:59

I have a database that stores everytime value as a numeric value. I need to know how to transform this into the HH:MM:SS format. I have been trying to do this for weeks and everytime i try i get more and more frustrated. If anybody can help i would greatly appreciate it.

Here is my SELECT
SELECT DATEPART(wk, DATEADD(ss, - 1, posting_time)) AS PTWeek, SUM(CAST(acd_t_abnd_prim AS INT)) AS W, SUM(CAST(acd_abnd_prim AS INT)) AS s

What i need to do is get the value of W/s in the HH:MM:SS format.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-17 : 14:03:32

Give some sample data and expected output

Splics
Starting Member

39 Posts

Posted - 2006-03-17 : 14:06:45

for W i get 99857 seconds
and for s i get 377 total calls

I am trying to get the average wait on these calls and to get that i take 99857/377 what i should get is 3:35

Splics
Starting Member

39 Posts

Posted - 2006-03-17 : 14:14:37

im sorry i should get 4:24

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-17 : 14:15:12

R u talking about SQL or Math ?
What is W? A field name ?
or u just want us to do some math ?
if u want the math tell us how does
99857/377 --> is 3:35

Splics
Starting Member

39 Posts

Posted - 2006-03-17 : 14:17:43

99857/377 = 264.87267 Seconds = 4.41544 Minutes ... i want to be able to convert this into the HH:MM:SS format

Splics
Starting Member

39 Posts

Posted - 2006-03-17 : 14:18:57

W is the total number of seconds
s is the total number of calls
This is my entire query

SELECT DATEPART(wk, DATEADD(ss, - 1, posting_time)) AS PTWeek, SUM(CAST(acd_t_abnd_prim AS INT)) AS W, SUM(CAST(acd_abnd_prim AS INT)) AS s
FROM dbo.HIST_ACD_GROUP_T
WHERE (posting_time <= CONVERT(DATETIME, '2007-01-01 00:00:00', 102)) AND (posting_time > CONVERT(DATETIME, '2006-01-01 00:00:00', 102)) AND
(DATEPART(wk, DATEADD(ss, - 1, posting_time)) = 3)
GROUP BY DATEPART(wk, DATEADD(ss, - 1, posting_time))

Splics
Starting Member

39 Posts

Posted - 2006-03-17 : 14:20:01

PTWeek W s
3 99857 377

this is my results set

Splics
Starting Member

39 Posts

Posted - 2006-03-17 : 14:20:31

PTWeek =3
W = 99857
s = 377

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-17 : 14:33:21

Splics, please go through ur all posts in this thread and c whether it makes any sense to you at least.

One time U say
>> 99857/377 what i should get is 3:35

then again u say
>> 99857/377 = 264.87267 Seconds = 4.41544 Minutes

Then u giving us some results.
????

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-17 : 14:35:20

Something like this?

declare @w int
declare @s int

set @w = 99857
set @s = 377

select cast(cast(@w * 1.0 / @s / 60 as int) as varchar(10)) + ':' + 
       cast(cast(@w * 1.0 / @s as int) % 60 as varchar(2))

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-17 : 14:39:14

Or, better still?

declare @w int
declare @s int

set @w = 99857
set @s = 377

select convert(varchar(10), dateadd(s, @w * 1.0 / @s, '1/1/1900'), 8)

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

Splics
Starting Member

39 Posts

Posted - 2006-03-17 : 14:43:43

That would be awesome but i am not doing this in a stored procedure. I am writing a report so i needs to be in the select query only.

jhermiz

3564 Posts

Posted - 2006-03-17 : 14:52:43

What kind of report? RS uses stored procedures and you can include tsql in them as they are apart of the sprocs.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Splics
Starting Member

39 Posts

Posted - 2006-03-17 : 15:46:30

It is in SQL Reporting Service 2005 ... really how would i do that?

swatib
Posting Yak Master

173 Posts

Posted - 2006-03-18 : 01:46:44

Splics, I just entered into this session.
Are you storing no. of seconds in the database and trying to show it as HH:MM:SS format?

Njoy Life

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-20 : 01:18:46

>> I am writing a report so i needs to be in the select query only.

In the Ryan code, dont use variables and change them to respective columns

Madhivanan

Failing to plan is Planning to fail

Splics
Starting Member

39 Posts

Posted - 2006-03-20 : 09:31:12

i try the ryan code and i end up getting divide by zero errors ... any help would be greatly appreciated

Splics
Starting Member

39 Posts

Posted - 2006-03-20 : 09:38:06

Both of the Ryan Codes give me divide by zero errors

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-20 : 09:39:09

then limit the query so there are no zeros in divison x/0 is illegal operation
you can do this if that's what you need:
isnull(yourcolumn, 1)
instead of just your column

Go with the flow & have fun! Else fight the flow

Splics
Starting Member

39 Posts

Posted - 2006-03-20 : 09:39:18

Yes Swatib ... the database is storing everything in seconds and i do want to show it in the HH:MM:SS format

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources