CODEWITHSUNDEEP
sqlsql-serverdatediff
ένας
ένας

Reputation: 115

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

Upvotes: 0

Views: 117

Answers (2)

Angus
Angus

Reputation: 41

The dateadd + datediff method returns a datetime which in full is 1900-01-01 02:55:46.000

So by using the dd in the format, you are taking the day from that, which is indeed 01

There are many ways to do what you want, but this is one way similar to yours:

CONVERT(char(2),FORMAT(DATEDIFF(dd,FirstDate,LastDate),'00')) + ' ' +  
CONVERT(varchar(8),CONVERT(time,LastDate - FirstDate))

(assuming you're using SQL server 2012+)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270483

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.

Upvotes: 1

Related Questions