How can I write this function to calculate datetime diff as hh:mm:ss:ms

Question

I am not a developer so please pardon my question. I am more of prod DBA

We have an SSRS page which displays the long running SQL queries and show elapsed time. But we needed a way to show duration like dd:hh:mm:ss:ms

Therefore i got the function from link here with code as below

--get the difference between two datetimes in the format: 'hh:mm:ss'
CREATE FUNCTION getDateDiff(@startDate DATETIME, @endDate DATETIME)
RETURNS VARCHAR(10)
AS BEGIN
    DECLARE @seconds INT = DATEDIFF(s, @startDate, @endDate)
    DECLARE @difference VARCHAR(10) =
    FORMAT(@seconds / 3600, '00') + ':' +
    FORMAT(@seconds % 3600 / 60, '00') + ':' +
    FORMAT(@seconds % 60, '00')
    RETURN @difference
END

But cant get this to work if i need day and milliseconds as well.

How can i use above to display dd:hh:mm:ss:ms or return data in this format? And we have to make sure it works for SQL2012 and +

IF there is a better code out there please guide me as i am novice to SQL dev part, thanks

Edit- Getting -ve value in seconds as mentioned in comment

With the 2nd edit i am seeing issues with day now:

Please see below

for start time "5/21/2021 8:00:23 PM" (ET) and end date "5/22/2021 01:09:0 6 AM"

it returned me 1 5:9:17.13 which is incorrect as should not be 1 day and 5 hours rather should be 5 hours so something is going wrong on day

You can can see that value is completely incorrect

Answer 1

The function returns varchar(10). The dd:hh:mm:ss:ms pattern is 14 characters.

Answer 2

it calculate datediff() in minute because using second or millisecond will surely cause overflow when the date is far apart.

declare @date_start datetime    = '2021-05-01',
        @date_en    datetime    = getdate()


select  [@date_start]   = @date_start,
        [@date_en]  = @date_en,
        concat(dy, ':', hr, ':', mn, ':', sc, '.', ms)
from    (
        select  dy = abs(diff_mins / 60 / 24),
                hr = diff_mins / 60 % 24,
                mn = diff_mins % 60,
                sc = datediff(second, dateadd(minute, diff_mins, @date_start), @date_en),
                ms = (datepart(millisecond, @date_en) - datepart(millisecond, @date_start) + 1000) % 1000
        from    (
                    select  diff_mins = datediff(minute, @date_start, @date_en)
                ) d
        ) d

dbfiddle

You also need to increase the size of the return string RETURNS VARCHAR(10) as dd:hh:mm:ss.mmm is 15 characters

---

EDIT : to handle where @date_start is later than @date_en

Also added a sg to indicate when such case, the sign is -

declare @date_start datetime    = '2021-05-31',
        @date_en    datetime    = getdate()


select  [@date_start]   = @date_start,
        [@date_en]  = @date_en,
        concat(sg, dy, ':', hr, ':', mn, ':', sc, '.', ms)
from    (
        select  sg = case when @date_start > @date_en then '-' else '' end,
                dy = abs(datediff(day, @date_start, @date_en)),
                hr = abs(diff_mins / 60 % 24),
                mn = abs(diff_mins % 60),
                sc = abs(datediff(second, dateadd(minute, diff_mins, @date_start), @date_en)),
                ms = (datepart(millisecond, @date_en) - datepart(millisecond, @date_start) + 1000) % 1000
        from    (
                    select  diff_mins = datediff(minute, @date_start, @date_en)
                ) d
        ) d

Answer 3

If you just output your two columns to SSRS, a TimeSpan can be used to get the difference between two DateTime values to great precision, but then... how to format it? Apparently SSRS can't handle TimeSpans directly in an expression, but the Code option can. Therefore:

1. Put a function like this into your report's Code block ("ff" is for the fractions of a second):

Function FormatTimeSpan(ByVal TS as TimeSpan) AS String

DIM Minus AS String
IF TS < TimeSpan.Zero THEN
   Minus = "-"
ELSE
   Minus = ""
   END IF
IF ABS(TS.TotalSeconds) >= 86400 THEN
   Return Minus & TS.ToString("d' days 'h\:mm\:ss\.ff")
ELSE
   Return Minus & TS.ToString("h\:mm\:ss\.ff")
   END IF

End Function

2. Put something like the following in your expression. In my case I wanted the elapsed time of computing the report. You would want to substitute fields for Now() and Globals!ExeuctionTime:

="Time to execute: " & Code.FormatTimeSpan(Now().Subtract(Globals!ExecutionTime))

Also noteworthy... the format strings for TimeSpan are different from those for (.NET)DateTime:

Custom TimeSpan Format Strings