Wrong Duration after Formatting DATEDIFF Output (Extra Day)

I need to get the duration between two points in time, and it’s needed in the format “DD HH:mm:ss”.

So, I used the following line:

FORMAT(CAST(CONVERT(varchar(20), DATEADD(SECOND, DATEDIFF(SECOND,FirstDate,LastDate), 0), 120) AS datetime), 'dd HH:mm:ss')

But it has an error. It returns an extra day. So, when both dates have the same day, it returns “01” in the days instead of “00”.

I hope someone can tell me where was I wrong.

Here’s my output:

FirstDate                   LastDate                    Duration
2020-09-24 08:20:42.843     2020-09-24 11:16:28.217     01 02:55:46
2020-09-24 08:20:42.437     2020-09-24 11:16:27.843     01 02:55:45
2020-09-24 08:20:41.373     2020-09-24 11:16:26.640     01 02:55:45

Answer

The date is wrong because it is a day of the month. You can do the time and days components separately:

select concat(v.secs / (60*60*24),
              ' ',
              convert(time(0), dateadd(second, v.secs % (60*60*24), 0))
             )
from (values (convert(datetime, '2020-09-24 08:20:42.843'),
convert(datetime, '2020-09-24 11:16:28.217'))) t(firstdate, lastdate) cross apply
     (values (DATEDIFF(SECOND, t.FirstDate, t.LastDate))) v(secs);

Here is a db<>fiddle.