CODEWITHSUNDEEP
sqlsql-serverdatetimedmv
Newbie-DBA
Newbie-DBA

Reputation: 107

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

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

enter image description here

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 enter image description here

Upvotes: 0

Views: 301

Answers (3)

Wayne Erfling
Wayne Erfling

Reputation: 306

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
  1. 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

Upvotes: 0

Squirrel
Squirrel

Reputation: 24793

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

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416111

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

Upvotes: 0

Related Questions