-1

I have a requirement to get time difference between two DateTime's in HH:MM format not decimal.

Eg: 

DECLARE @D1 DATETIME = '2019-11-18 06:00:00', @D2 DATETIME = '2019-11-19 10:23:00'

I need Time Diff as 28:23 not with decimals. I found related article here but not forDATETIME format. Any suggestion

Thom A
  • 88,727
  • 11
  • 45
  • 75
Aditya Prasad
  • 212
  • 1
  • 2
  • 11
  • I'm having format deviation compared to provided/suggested URL's. Why is my query closed with related info but not exact answer – Aditya Prasad Nov 20 '19 at 12:40

1 Answers1

-1

you can try like below

DECLARE @D1 DATETIME = '2019-11-18 06:00:00', 
@D2 DATETIME = '2019-11-19 10:23:00' 
select convert(varchar(5),DateDiff(s, @D1, @D2)/3600)+':'+convert(varchar(5),DateDiff(s, @D1, @D2)%3600/60)

output

col
28:23
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63
  • I'm getting -ve value for duration at this example. Please check `DECLARE @D1 DATETIME = '2019-11-18 11:22:45.277', @D2 DATETIME = '2019-11-18 12:09:35.103' select concat( datediff(hour,@D1,@D2),':', cast( round( (cast(datediff(second,@D1,@D2)/3600.00 as decimal(10,5))-cast ( datediff(hour,@D1,@D2) as int))*60,1) as int))` – Aditya Prasad Nov 20 '19 at 12:09
  • 1
    This should work `DECLARE @D1 DATETIME = '2019-11-18 11:22:45.277', @D2 DATETIME = '2019-11-18 12:09:35.103' select convert(varchar(5),DateDiff(s, @D1, @D2)/3600)+':'+convert(varchar(5),DateDiff(s, @D1, @D2)%3600/60)` – Shalem Nov 20 '19 at 12:30
  • @Shalem thanks i have added your comments as a answer – Zaynul Abadin Tuhin Nov 21 '19 at 06:49
  • @Shalem - That work's for me. Thanks – Aditya Prasad Nov 23 '19 at 06:58