Report Time in proper format

I was working on a restore script to test our backups and also, made a reporting services report which will list out what backups have been used to restore the database,how long each one took and size of each backup. While the report was very helpful, it was kind of very hard to understand the restore time and backup sizes. The main reason for this is because some databases are kind of very small compared to others,so,their backup sizes and restore times are much lesser than others.

Below post is on how I addressed restore time issue in my report.I also made similar function to report on backup size and you can read that HERE.

I was initially reporting the time in secs but as mentioned the bigger databases were taking almost 2 hrs to restore, so, restore time shows this as 7200. It is easy to understand 7200 seconds as 2 hrs. So, to fix this, I made a scalar function which takes input time in seconds and returns time in hh:mm:ss format.

For Example: If restore took 240 seconds, it will return 00:04:00.
If restore took 7200 seconds, it will return 2:00:00

Below is the function:

USE [MSDB]
GO
CREATE FUNCTION [dbo].[fncTime] (@TimeIn int)
RETURNS varchar(11)
AS
Begin
declare @Hr int
declare @hold int
declare @TimeOut varchar(11)
IF (@TimeIn<60)
Begin
select @Timeout = '00:00:'+right('0'+cast(@TimeIn as varchar(2)),2)
End
IF (@TimeIn>=60 and @TimeIn<=3600)
Begin
select @TimeOut = '00:'+right('0'+cast((@TimeIn/60) as varchar(2)),2) +':'+right('0'+cast((@TimeIn%60) as varchar(2)),2)
End
IF (@TimeIn>=3600)
Begin
select @Hr=@TimeIn/3600
select @Hold =@TimeIn%3600
IF (@Hold)>0
Begin
select @TimeOut = case when @Hold>60 then cast(@hr as varchar(5)) +':'+
right('0'+cast((@Hold/60) as varchar(2)),2)+':'+
right('0'+cast((@Hold%60) as varchar(2)),2)
else cast(@hr as varchar(5)) +':00:'+right('0'+cast((@Hold%60) as varchar(2)),2) End
End
Else
Begin
Select @TimeOut = cast(@hr as varchar(5)) +':00:00'
End
End
RETURN @TimeOut
End

Output : select msdb.dbo.[fncTime](45085) as [RestoreDuration(hh:mm:ss)]

Advertisement