Format Two Datetimes’ Difference in [days in variable length] dd:hh:mm:ss (T-SQL)



--[hh:mm:ss]
create table test (Caseid int, indate datetime, outdate datetime)

Insert into test values(1,'2013-09-17 10:09:22.000' ,'2013-09-17 11:12:22.000')

Select Caseid, Convert(varchar(10), outdate-indate , 108) as [hh:mm:ss] from test t

SELECT Convert(varchar(8), dateadd(second, datediff(second, indate,outdate) ,'1900-01-01'), 108) as [hh:mm:ss]
from test
drop table test

----************************************
----**********************************

Declare @StartDate as datetime ='8/21/2013'
Declare @EndDate as datetime = current_timestamp --GETDATE()
--='8/18/2013'

SELECT @StartDate, @EndDate
, Coalesce( Convert(varchar(5),DateDiff(day, (@EndDate-@StartDate),'1900-01-01')) + ':'
+ Convert(varchar(10),(@EndDate-@StartDate), 108),'00:00:00:00') as [days:hh:mm:ss]

--SQL Server 2012 with FORMAT function
,Coalesce( Convert(varchar(5),DateDiff(day, (@EndDate-@StartDate),'1900-01-01')) + ':' +
FORMAT((@EndDate-@StartDate), 'HH:mm:ss') ,'00:00:00:00') as [SQL Server 2012--days:hh:mm:ss]

--Another sample for converting minutes to [hh:ss]

declare @diffinMinutes int=165

SELECT Convert(varchar(5),(dateadd(minute, @diffinMinutes,'1900-01-01')), 108) as [hh:mm]

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s