How to convert integer(seconds) to time using SQL Server query or function

Integer to Time Conversion using SQL Query

Before going to use below script please go run this SQL scripts for creation of tables with data.


Some of the times we have to convert integer to time. So, here i am giving one example query. This query will take seconds as input and convert into time format as output.

1 Query:

CREATE function fnGetMintsFromInt(@secs int)  
returns  varchar(10)  
as
 begin  
   declare @totalTime varchar(20)  
   set @totalTime =    CASE WHEN @secs/3600<10 THEN '0' ELSE '' END   + RTRIM(@secs/3600)  + ':' + RIGHT('0'+RTRIM((@secs % 3600) / 60),2)  + ':' + RIGHT('0'+RTRIM((@secs % 3600) % 60),2)  
   return(@totalTime) 
 end 

select dbo.fngetmintsfromint(100)as Time

In the above example i have given 100 seconds as input we will get one minute and forty seconds as output.
Output:
Time
00:01:40



2 Query: In this example we will get milli-seconds as well. 

DECLARE @Sec INT 
SET @Sec = 100 
SELECT CONVERT(VARCHAR(11),DATEADD(s,@Sec,0),14) 

Output:
Time
00:01:40:00

For go through more SQL Server Interview Questions