duration into seconds - 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 2008 Forums

Transact-SQL (2008)

duration into seconds

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-11-23 : 04:11:16

I have a varchar column which is saving the duration as
HH;mm:ss:fff like '00:01:02:434'
now in the result set along with duration i need a column which should convert this into total number of seconds
How can i do that?

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-23 : 04:20:19

[code]
select datediff(second, 0, '00:01:02:434')
[/code]

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

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-11-23 : 04:28:43

For '00:01:02:434'
Can I get the result as 63 seconds instead of 62 seconds

Actually I have some situations where i can have value as '00:00:00:034'
so I wanted to see it atleast 1

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-23 : 04:31:28

always round up ?


select	ceiling(datediff(millisecond, 0, '00:01:02:434') / 1000.0)

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

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-11-23 : 04:35:29

not working correctly

showing 00:00:05:769 as 1

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-23 : 04:43:37

quote:
Originally posted by kamii47

not working correctly

showing 00:00:05:769 as 1

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

Not in my SQL Server


select	ceiling(datediff(millisecond, 0, '00:00:05:769') / 1000.0)

RESULT                    
------------------- 
6

(1 row(s) affected)

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

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2011-11-23 : 05:02:43

Ok Sorry.
I haven't change the seconds to milliseconds

Kamran Shahid
Sr. Software Engineer
(MCSD.Net,MCPD.net)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-23 : 05:04:23

no problem

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources