0

I want to subtract two datetime values and the output in HH:MM:SS format.

If the difference is more than one day, the days count needs to be added to hours.

I tried to concat the datediff outputs as mentioned below

select concat(datediff(hh, '2019-07-16 04:45:03.000','2019-07-17 19:38:13.000')
,':',
(datediff(mi, '2019-07-16 04:45:03.000','2019-07-17 19:38:13.000') -(datediff(hh, '2019-07-16 04:45:03.000','2019-07-17 19:38:13.000')*60))
,':',
(datediff(ss, '2019-07-16 04:45:03.000','2019-07-17 19:38:13.000')-(datediff(mi, '2019-07-16 04:45:03.000','2019-07-17 19:38:13.000')*60)))

Expected output: 38:53:10

Actual output: 39:-7:10

Can you tell me what mistake I made? Is there a better way to get the required output?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Venkat
  • 47
  • 7
  • `time` doesn't support values over 24 hours. You'll be better off returning the difference in seconds and having your display layer worry about the formatting, or similar. What application are you using for the display layer? That's what you *should* be tagging here. – Thom A Jul 17 '19 at 18:48
  • This seems like a duplicate of this question: https://stackoverflow.com/questions/10538507/days-hours-minutes-seconds-between-two-dates – pmbAustin Jul 17 '19 at 18:55
  • @Larnu Oh okay fine, thanks you. The answer provided by David Browne works fine. – Venkat Jul 17 '19 at 19:03
  • Yes, but the problem with that solution, @Venkat, is that it returns a `varchar`, and as a result, the "time" `'11:00:01'` with be **after** `'100:00:00'`. – Thom A Jul 17 '19 at 20:04
  • Oh, and it uses `FORMAT`, which (i'll be honest), is a terribly poor performer. – Thom A Jul 17 '19 at 20:06

1 Answers1

1

Something like:

with q as
(
  select datediff(SECOND, '2019-07-16 04:45:03.000','2019-07-17 19:38:13.000') secs
), hms as
(
  select secs/60/60 as hours,  (secs/60) % 60 as minutes , secs % 60 seconds
  from q
)
select concat(format(hours,'00'),':',format(minutes,'00'),':',format(seconds,'00') ) hms
from hms

which returns

38:53:10
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Browne Thank you so much, this works fine. I have a couple for questions, can you please clarify.? 1.) What's wrong with the query which i has posted? 2.) What does " format(hours,'00') " do? – Venkat Jul 17 '19 at 19:02
  • Careful with `FORMAT`; it perforums awfully. You would be far better off avoiding it. – Thom A Jul 17 '19 at 20:07
  • If perf is critical, something like `right(concat('00',hours),2)` would be faster. – David Browne - Microsoft Jul 17 '19 at 20:14
  • That would be far better, in my opinion @DavidBrowne-Microsoft . – Thom A Jul 17 '19 at 20:15
  • Why do I have to use format here, what would be the issue with just concatting hours, minutes and seconds from hms in the query directly. – Venkat Jul 18 '19 at 02:16
  • Just string formatting. You can use whichever you prefer. The example had all 2 digit numbers, so it was unclear which format was required. – David Browne - Microsoft Jul 18 '19 at 10:54