Conversion of 2 columns from secs to HH:MM:SS`

Question

Conversion of 2 columns from secs to HH:MM:SS`

Kazmerelda

SSCarpal Tunnel

Points: 4667
More actions
February 10, 2011 at 5:26 am

#245096

Hey,
I have searched on here/various places but nothing is working for the above.
I have 2 columns response time and fix time. Both are in seconds. However I need both to be HH:MM:SS.
I have tried various things with conversions but when I run the report the value stays the same in each of the columns. I assume it is because I am not telling it to look at those columns but I am abit stumped as to what I need to do next.
I did initially put this:
DECLARE @seconds INT
SET @seconds = 3723
SELECT CONVERT(CHAR(8), DATEADD(SECOND, @Seconds, '00:00:00'), 108)
then the query but this didn't work.
Any help is greatly appreciated. Thanks in advance no doubt I am overthinking this!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you say the query doesn't work...but your calculation seems to be returning a value...

I'm just guess, maybe it wasn't obvious, but you can substitute the @variable for a column name to do what you were after...is that it?

otherwise, can you explain what exactly didn't work?

here's your example selecting from a table, as well as a couple of other versions:

/*--Results

(No column name) (No column name) (No column name)

01:02:03 1900-01-01 01:02:03.000 01:02:03

01:20:55 1900-01-01 01:20:55.000 01:20:55

03:27:37 1900-01-01 03:27:37.000 03:27:37

*/

With MyTable(TheSeconds)

AS

(

select 3723 UNION ALL

select 4855 UNION ALL

select 12457

)

SELECT

CONVERT(CHAR(8), DATEADD(SECOND, TheSeconds, '00:00:00'), 108),--returns 01:02:03

dateadd(second,TheSeconds,0), --returns 1900-01-01 01:02:03.000

CONVERT(CHAR(8), DATEADD(SECOND, TheSeconds,0), 108) --returns 01:02:03

From MyTable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Kazmerelda SSCarpal Tunnel Points: 4667 More actions · Answer 2

Hi Lowel,

Thanks for your response, I did wonder if I had given enough information so here is the whole query that I have run:

DECLARE @Hours int

DECLARE @minutes int

DECLARE @seconds INT

SET @seconds = 3723

SELECT CONVERT(CHAR(8), DATEADD(SECOND, @Seconds, '00:00:00'), 108)

use DB

select callref, customer, logged on, closed on, info, response time, fix time,closedby

from calltable

where status = 'CLOSED'

and supportgroup in ('teamA','TeamB')

and month (date_time) = '4'

and year (date_time) = '2010'

order by closedby,callref

Are you saying the the declare items I should add in response time and fix time instead?

When I run the above I get 2 sets of results in SSMS, one which says 01:02:03 and then the results of the query with the fields in seconds instead of HH:MM:SS

Thanks for the help it is appreciated!

Lowell SSC Guru Points: 323518 More actions · Answer 3

your post made it a lot clearer, thanks.

i think this is what you want to do: use the formula you created on the two columns themselves...

select

callref,

customer,

[logged on],

[closed on],

info,

--[response time],

--[fix time],

CONVERT(CHAR(8), DATEADD(SECOND, [response time], '00:00:00'), 108) As [response time],

CONVERT(CHAR(8), DATEADD(SECOND, [fix time], '00:00:00'), 108) As [fix time],

closedby

from calltable

where status = 'CLOSED'

and supportgroup in ('teamA','TeamB')

and month (date_time) = '4'

and year (date_time) = '2010'

order by

closedby,

callref

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Kazmerelda SSCarpal Tunnel Points: 4667 More actions · Answer 4

Lowell thank you so much I think I was staring at it too long and couldn't see the wood for the trees!