DateDiff in HH:mm:ss - 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

General SQL Server Forums

New to SQL Server Programming

DateDiff in HH:mm:ss

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.000

Duration = 10:05:00

Thank 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 MVP
http://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 apart
ALso Format works only from SQL 2012 onwards

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://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 MVP
http://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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources