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

Pad then Add Leading Zeros

In the event you need to add leading zeros to an integer, the process is pretty straightforward. I am using the AdventureWorks database and SQL Server 2008 R2. For this example I will use the Sales.SalesPerson table for their SalesPersonID field which contains 3 digit IDs. First let’s take a look at the data. As you can see the data looks fine so let’s get started.

SELECT SalesPersonID
FROM [AdventureWorks].[Sales].[SalesPerson]

Padding

The first thing we need to do is to pad the field with leading spaces using the STR function and specify 6 spaces.

SELECT STR(SalesPersonID, 6) 'SalesPersonID'
FROM [AdventureWorks].[Sales].[SalesPerson]

Replacing

Now we just need to replace those spaces with zeros using the REPLACE function

SELECT REPLACE(STR(SalesPersonID, 6),SPACE(1),'0') 'SalesPersonID'
FROM [AdventureWorks].[Sales].[SalesPerson]

That’s it… nothing to it.

Update your Skill-Set and Stay Marketable in IT

It’s no secret that you need to stay on top of the latest technologies in order to stay marketable, but how do you do it on a tight budget? For one you cannot just go out and purchase the most beefy server(s) available because the hardware alone can cost at minimum two-three months rent and that’s a scaled down version. The server’s I often dream about are way out of my budgetary league. Then you have the software and licensing to add to the piling list of expenses. So where do you turn? How about virtualization? After all virtualization is booming so while you’re practicing Active Directory management or SQL Server Mirroring you can learn a few things about virtualization in the process. And that my friend is what I consider a true win-win scenario!

Virtualizing as I like to call it is essentially running or hosting a guest operating system on top of another system. Your current system should have at least 4GB of RAM and running at least an Intel Core2Duo processor or comparable AMD. I mean you could make do with less but performance will suffer. Don’t get me wrong virtualization can be costly but there are affordable versions available. I am bias to VMware and I love Workstation 7 but it comes with a price. However VMware Server is (FREE), as well as VirtualBox (FREE) by SUN and VirtualPC (FREE) by Microsoft to name a few. I have used all of the software listed and found that VMware has the fastest load times. I am talking about installing and loading the Operating System form an ISO CD image. Honestly in the end they all accomplished the same results so no matter what you choose you will still reap benefits of virtualization. Now you just need to get your hands on some operating systems and server software.

Good news… if you are a college student you have some options. Academic versions are often offered at significantly reduced prices. If that is not an option you can sign up for Dreamspark.com and get software for free, but you need an active college email address to validate your student status. At the very least you have the trials editions offered by Microsoft that are readily available but you will be limited to 120 days. You can always reseal the OS and extend your trial a bit further so keep that in mind. With either software option you should get enough experience and understanding with the OS to hold an intelligent conversation about the subject matter.

Some people tend to think that their company should invest in them and pay for technical training when in reality you really need to invest in yourself. It speaks volumes about a person who is able to tackle new functionality from new releases let alone get certified without the aided help of the employer. Honestly how else are you supposed to get ahead and advance your career without putting forth the effort to learn on your own? Trust me education through osmosis does not work so don’t just by a book hoping it will make you smarter. You have to open it and read the pages to get the most out of that investment. Not to mention picking up some reading material is always a good idea and it’s also a great way to work towards certification. If all else fails you can always turn to google or bing and they are both filled with tons of useful resources.

If at all you are into SQL Server you can download SQL Server 2008 R2 Express which will provide you with the tools necessary to perform the install, configurations including settings and security, restores, backups, business intelligence and well as diving into the Transact-SQL query language. However if you want to work with mirroring, replication, high availability then you can opt for Developer Edition which is typically $50. It provides all the functionality of Enterprise edition with limitations of course. So you can create database snapshots, setup principal and mirror servers, setup a publication, remote or local distributors and other good stuff. So what are you waiting for? It’s at your grasp… so grab a hold of it and run with it!