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.
Author |
Topic |
stharish
Starting Member
43 Posts |
Posted - 2014-03-02 : 15:14:16
|
Given the two datetimes below, what's the best way to obtain the total duration in hours, minutes and seconds (HH:mm:ss)?Start Time: 2014-03-02 20:55:00.000 End Time: 2014-03-03 07:00:00.000Duration = 10:05:00Thank you! |
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2014-03-02 : 19:58:44
|
[code]DECLARE @StartTime Datetime = '2014-03-02 20:55:00.000', @EndTime Datetime = '2014-03-03 07:00:00.000' SELECT CONVERT(VARCHAR(12), @EndTime - @StartTime, 114) AS Duration[/code] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-03-03 : 04:10:00
|
[code]DECLARE @StartTime Datetime = '2014-03-02 20:55:00.000', @EndTime Datetime = '2014-03-05 07:00:00.000' SELECT CAST(DATEDIFF(seconds,@StartTime,@EndTime)/60*60 AS varchar(5)) + ':' + RIGHT('00' + CAST((DATEDIFF(seconds,@StartTime,@EndTime)%3600)/60 AS varchar(2)),2) + ':'+ RIGHT('00' + CAST((DATEDIFF(seconds,@StartTime,@EndTime)%3600)%60 AS varchar(2)),2) [/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
maunishq
Yak Posting Veteran
71 Posts |
Posted - 2014-03-03 : 13:20:50
|
DECLARE @StartTime Datetime = '2014-03-02 20:55:00.000', @EndTime Datetime = '2014-03-03 07:00:00.000'SELECT FORMAT(@EndTime-@StartTime, 'HH:mm:ss') AS Duration!_(M)_! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-03-03 : 14:48:29
|
quote: Originally posted by maunishq DECLARE @StartTime Datetime = '2014-03-02 20:55:00.000', @EndTime Datetime = '2014-03-03 07:00:00.000'SELECT FORMAT(@EndTime-@StartTime, 'HH:mm:ss') AS Duration!_(M)_!
Wont work if dates span more than 24 hrs apartALso Format works only from SQL 2012 onwards------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-03-03 : 16:49:05
|
quote: Originally posted by visakh16
DECLARE @StartTime Datetime = '2014-03-02 20:55:00.000', @EndTime Datetime = '2014-03-05 07:00:00.000' SELECT CAST(DATEDIFF(second,@StartTime,@EndTime)/3600 AS varchar(5)) + ':' + RIGHT('00' + CAST((DATEDIFF(second,@StartTime,@EndTime)%3600)/60 AS varchar(2)),2) + ':'+ RIGHT('00' + CAST((DATEDIFF(second,@StartTime,@EndTime)%3600)%60 AS varchar(2)),2) ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs
Just a couple of small corrections. |
|
|
stharish
Starting Member
43 Posts |
Posted - 2014-03-04 : 00:44:29
|
This works great! Thank you all who replied! |
|
|
|
|
|
|
|