How to convert time? - SQL Server Forums

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

Transact-SQL (2005)

How to convert time?

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2008-05-30 : 13:48:09

I have a field that displays seconds. I need to convert it to hours/min/sec.

For example,


Seconds convertedValue
90       1 min 30 sec 
60       1 min 0  sec
95       1 min 35 sec

Is this possible? Or maybe this would be easier:


Seconds convertedValue
90       00:01:30
60       00:01:00
95       00:01:35

Thanks,
Ninel

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-30 : 14:16:18

http://www.sqlservercurry.com/2008/03/how-to-convert-seconds-to-hhmmss.html

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-05-30 : 18:18:44

[code]select
MySeconds,
MyTime =
case
when MySeconds < 86400
then
convert(varchar(8),dateadd(ss,MySeconds,0),108)
else
convert(varchar(20),datediff(hour,0,dateadd(ss,MySeconds,0)))+
right(convert(varchar(8),dateadd(ss,MySeconds,0),108),6)
end
from
(
--TestData
select MySeconds = 86400*20 union all
select MySeconds = 86400 union all
select MySeconds = 86399 union all
select MySeconds = 60 union all
select MySeconds = 90 union all
select MySeconds = 95
) a

Results:
MySeconds MyTime
----------- --------------------------
1728000 480:00:00
86400 24:00:00
86399 23:59:59
60 00:01:00
90 00:01:30
95 00:01:35

(6 row(s) affected)

[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources