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

@Charlieface : Not really the workload queries but yes connections from sp_server_diagnostics and in case monitoring server running there would be trace related showing running let’s say since last reboot which I have seen is 30 days. Yes we can exclude such process but just in case wanted to know if one do not want to exclude them — Newbie-DBA, May 19 '21 at 02:39

Squirrel · Answer 1 · 2021-05-22T06:47:00.687

1

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

edited May 22 '21 at 06:47

answered May 16 '21 at 02:28

Squirrel

23,507
4
34
32

@SQuirrel- Sorry i unmarked as answer as i ran into issues with above code. The seconds show up in (-) negative values. Example date_start in our case comes as input 2021-05-20 07:18:56:000. Will apprecitae if you can guide me how to fix (-ve) value coming in seconds – Newbie-DBA May 20 '21 at 21:42
use `ABS()` on the values – Squirrel May 21 '21 at 01:28
Edited the answer with necessary changes – Squirrel May 21 '21 at 01:33
Thanks that very much helps. Some data still shows day in -ve. while hh mm ss and ms does not show that conflict – Newbie-DBA May 21 '21 at 17:26
for `@date_start` is later than `@date_en` case, how do you want it ? Provide a sample data and expected result – Squirrel May 22 '21 at 02:21
@Squirell, i think time diff is fraction of seconds when start may be later than end date. I have to check why that could be the case. From your last edit everything looked good, its just sometimes day is returned as -0. so its like -0:3:43:23:234 rather it should be 0 3:43:23:234: Thank you again so for being prompt on your responses – Newbie-DBA May 22 '21 at 04:33
it will be good if you can also provide the start and end date time that resulted in this – Squirrel May 22 '21 at 04:43
Just added the issue i am seeing where day is returned as 1 while its just been 5 hours. So something is not correct around day – Newbie-DBA May 22 '21 at 05:14
Thank and appreciate you checking every time but still see-ve See below Start date 2021-05-31 00:00:00.000 end date-2021-05-24 21:46:09.377 difference -7:2:14:9.376 – Newbie-DBA May 24 '21 at 20:47
When start date is later than end date, it will show as negative. If you only want the absolute value, remove the `sg` from the `concat` – Squirrel May 25 '21 at 01:57

score 0 · Answer 2 · answered May 16 '21 at 02:11

0

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

answered May 16 '21 at 02:11

Joel Coehoorn

399,467
113
570
794

score 0 · Answer 3 · answered Mar 28 '23 at 19:20

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:

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

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

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

3 Answers3