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
Share this post :

Post a Comment

Please give your valuable feedback on this post. You can submit any ASP.NET article here. We will post that article in this website by your name.

 
Support : Ranga Rajesh Kumar
Copyright © 2012. ASP.NET Examples - All Rights Reserved
Site Designed by Ranga Rajesh Kumar