Format Seconds into HH:MM:SS

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.

Calculating Hours

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.

SELECT (3723/3600)

Calculating Minutes

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)

Calculating Seconds

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s