-2

Does anyone have a function that converts date difference between two dates into timer format ?

For example:

select udf_DateDiffTimer ('2020-05-12 14:12:34','2020-05-12 14:12:35')

The result should be char string: '00:00:01' (1 sec)

select udf_DateDiffTimer ('2020-05-12 14:12:34','2020-05-12 15:26:55')

The result should be char string: '01:14:21' (1 h 14 min 21 sec)

It should not be hard to write it myself, just want to make sure I do not re-invent the wheel

1
  • for anyone who downvote, please explain what is wrong with this Question, so at least I won't create such questions in future... – Aleksey Vitsko May 12 '20 at 13:00
2

If you have to go above counting to 24h you could use this

DECLARE @startdate datetime2 = '2020-05-12 15:26:54';
DECLARE @enddate datetime2 = '2020-05-12 15:26:55';
DECLARE @Seconds int = datediff(s,@startdate,@enddate);
SELECT CAST(@Seconds/3600 AS VARCHAR(10))
      + RIGHT(CONVERT(CHAR(8),DATEADD(s,@Seconds,0),108),6);

Referenced source

If you only have to go up to 24h:

DECLARE @startdate datetime2 = '2020-05-11 15:26:55';
DECLARE @enddate datetime2 = '2020-05-12 15:26:55';
DECLARE @Seconds int = datediff(s,@startdate,@enddate);

SELECT CASE WHEN @Seconds >= 86400 THEN '24:00:00' 
            ELSE CONVERT(CHAR(8),DATEADD(s,@Seconds,0),108) 
            END as TimeDifference;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.