Conversion of Seconds to HH:MM:SS format

Recently I faced a requirement of converting seconds to HH:MM:SS format. After some R&D, I found the following solution:

CODE

DECLARE @in_seconds int

SET @in_seconds = 3661 -- One Hour One Minute and One Second

SELECT CONVERT(CHAR(8), DATEADD(SECOND, @in_seconds, 0), 108) As Hour_Minute_Second

OUTPUT

01:01:01

Note: This SQL code is applicable only for time less than 24 hours.

To overcome this limitation of 24 hours, I created the following function, which has the ability to return correct time duration for large time duration in seconds:

USE [AdventureWorks]
GO
/****** Object:  UserDefinedFunction [dbo].[fnc_convert_seconds_to_HHMMSS]    Script Date: 10/02/2013 08:48:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnc_convert_seconds_to_HHMMSS]
(
@dc_time decimal(18,2)
)

RETURNS VARCHAR(20)

AS

BEGIN
RETURN REPLACE(STR(CONVERT(INT,@dc_time/3600), LEN(LTRIM(CONVERT(INT,@dc_time/3600)))) + ':' + STR(CONVERT(INT,(@dc_time/60)%60), 2) + ':' + STR(@dc_time%60, 2), ' ', '0')
END

Example: SELECT dbo.fnc_convert_seconds_to_HHMMSS(36460) AS vc_time_in_HHMMSS

Output: 10:07:40

1 comment:

  1. Yes, you are absolutely correct.The majority of people outsource the process of ASP.NET web design.
    That is mainly because of professionalism and expertise. money, valuable and time solutions,
    rarely does any organization dare to get started on especial on-site functions with this as this entails large chance and might lead to throw away of time.Foe more info kindly visit
    Dot Net Training Academy in Chennai

    ReplyDelete