Without having to use replication monitor I decided to put together a stored procedure that will return the same results. In the MSmerge_sessions table of the Distribution database the field duration is returned in raw seconds. Meaning that if the process took 2 mins and 2 secs it displayed as 122. Well I suppose I could live with it but I am hard-headed and wanted the outcome to look similarly formatted HH:MM:SS. Without really getting into the specifics on joining tables I used the MSmerge_Agents a and MSmerge_Sessions s joining on s.agent_id to a.id and added a.subscriber_name to my query to display the subscriber name but that is beside the point of this post.
The first thing you should know is how to calculate seconds into hours and minutes. Below outlines how to do each, but before we jump into that let’s try it manually.
There are 60 seconds in one minute and there are 60 minutes in one hour. So it is 60*60 which equals 3600. 3600 is the amount of seconds in one hour.
Now let’s take 3723 seconds and divide it by 3600.
What do we get?
3723/3600 = 1.03416 just a tad more than one hour
If you run the following in Management Studio you will notice that one is returned. You will not get (.03416) trailing the one. So calculating hours is easy since it returns whole numbers.
In order to calculate minutes you will need to perform a calculation similar to the one above with the exception of asking for the remainder using the modulo operator. Which is stated as: (3723 % 3600). However this alone will not return the results you are looking for. You need to take it a step further and divide it by 60.
Now we have:
((3723 % 3600) / 60) = 2 minutes
If you run the following in Management Studio you will get the same result as I stated above.
SELECT ((3723 % 3600) / 60)
Alright we are almost there. Now we need to get the seconds after everything is all said and done. This is another easy one. We take 3723 and get the remainder by using 3723 % 60.
SELECT (3723 % 60)
Run it together
Here is a quick and non-formatted way to see the results.
DECLARE @secs INT SET @secs = 3723 SELECT (@secs / 3600) 'Hrs' SELECT ((@secs % 3600)/60) 'Mins' SELECT (@secs % 60) 'Secs'
Stop! Format Time!
The purpose of this post was to format this field appropriately and here is what I did.
DECLARE @secs INT SET @secs = 3723 SELECT CONVERT(VARCHAR(10),CONVERT(datetime,CAST((@secs / 3600) AS VARCHAR) + ':' + CAST(((@secs % 3600)/60) AS VARCHAR) + ':' + CAST((@secs % 60) AS VARCHAR)),108)
Final Results Set