# 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)]