Converting seconds to Hours!!!

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

General SQL Server Forums

New to SQL Server Programming

Converting seconds to Hours!!!

Author

Topic

dseb
Starting Member

7 Posts

Posted - 2012-03-13 : 22:30:52

Hey everyone,
I have a table of which one of the columns has data converted into seconds. I'm trying to get this data converted to Hours but find it very confusing because...
In that column one row may have
25200;39600;57600;
and the next
25200;39600;57600;
All times represented in seconds. Which I want displayed in Hours. For example that 1st row should read 7;11;16 (military time). How can I use this column in my query and convert that data into hours?
Thx

dSEB

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-13 : 22:41:06

just convert to hour ? what about minute / seconds ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-13 : 22:49:35

why is value stored in delimited format? have you heard about normalisation and first normal form?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-13 : 22:51:26

[code]
-- sample table
declare @sample table
(
col varchar(100)
)

-- sample data
insert into @sample (col) select '25200;39600;57600'
insert into @sample (col) select '21600;25200;28800'

-- Query
select col, col2 = stuff(hr, 1, 1, '')
from @sample s
cross apply
(
select ';' + datename(hour, dateadd(second, convert(int, Data), 0))
from dbo.fnParseList(';', col) -- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033
for xml path ('')
) h (hr)

-- Result
/*
col col2
----------------- -------
25200;39600;57600 7;11;16
21600;25200;28800 6;7;8
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

dseb
Starting Member

7 Posts

Posted - 2012-03-13 : 23:42:15

Only need the hour. just want all those numbers divided by 3600.

quote:
Originally posted by khtan

just convert to hour ? what about minute / seconds ?

KH
[spoiler]Time is always against us[/spoiler]

dSEB

dseb
Starting Member

7 Posts

Posted - 2012-03-13 : 23:43:06

I don't even know what 'delimited format means'

quote:
Originally posted by visakh16

why is value stored in delimited format? have you heard about normalisation and first normal form?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dSEB

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-13 : 23:46:41

what you have is delimited format

delimiter = ";"
this is delimited format "25200;39600;57600;"

KH
[spoiler]Time is always against us[/spoiler]

dseb
Starting Member

7 Posts

Posted - 2012-03-13 : 23:48:12

Thx guys but you all talking tom me in a foreign language

dSEB

dseb
Starting Member

7 Posts