SQL Server: Calculate TimeSpan using TSQL - dd:hh:mm:ss

While coding on my new project, wanted to show the duration between two DateTime values as a Time Span like Remaining Days : Remaining Hours : Remaining Minutes : Remaining Seconds (dd:hh:mm:ss).

There are multiple solutions available in the net and you can certainly use one of them as long as they satisfy your requirements. However, this is what I wrote, it may be useful if you need the same.

view plainprint?

1. DECLARE @d1 datetime = '2016-12-24T00:44:01.000'  
2. DECLARE @d2 datetime = '2016-12-25T01:43:00.000'  
3.   
4. SELECT   
5.  CASE WHEN LEN(DATEDIFF(ss, @d1, @d2) / 86400) = 1 THEN '0' + Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) / 86400)) ELSE Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) / 86400)) END   
6.  + ':' +  
7.  CASE WHEN LEN((DATEDIFF(ss, @d1, @d2) % 86400) / 3600) = 1 THEN '0' + Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 86400) / 3600) ELSE Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 86400) / 3600) END    
8.  + ':' +  
9.  CASE WHEN LEN((DATEDIFF(ss, @d1, @d2) % 3600) / 60) = 1 THEN '0' + Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 3600) / 60) ELSE Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 3600) / 60) END   
10.  + ':' +  
11.  CASE WHEN LEN(DATEDIFF(ss, @d1, @d2) % 60) = 1 THEN '0' + Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 60)) ELSE Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 60)) END   

DECLARE @d1 datetime = '2016-12-24T00:44:01.000'
DECLARE @d2 datetime = '2016-12-25T01:43:00.000'

SELECT 
 CASE WHEN LEN(DATEDIFF(ss, @d1, @d2) / 86400) = 1 THEN '0' + Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) / 86400)) ELSE Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) / 86400)) END 
 + ':' +
 CASE WHEN LEN((DATEDIFF(ss, @d1, @d2) % 86400) / 3600) = 1 THEN '0' + Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 86400) / 3600) ELSE Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 86400) / 3600) END  
 + ':' +
 CASE WHEN LEN((DATEDIFF(ss, @d1, @d2) % 3600) / 60) = 1 THEN '0' + Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 3600) / 60) ELSE Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 3600) / 60) END 
 + ':' +
 CASE WHEN LEN(DATEDIFF(ss, @d1, @d2) % 60) = 1 THEN '0' + Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 60)) ELSE Convert(varchar(2), (DATEDIFF(ss, @d1, @d2) % 60)) END 

Here are some examples used with above code;