Convert seconds to '00:00:00' format - SQL Server

Question

I have a report in which i have to calculate overall worked hours by any employee in a week. The report reflects hours more than 24 hours(or 86400 seconds). I want to convert all the seconds into "00:00:00" format keeping the format of the columns as datetime or time so that I can do further analysis using the time field.

Below is what I have tried so far:

Declare @WorkTimeInSeconds int
Set @WorkTimeInSeconds = 144000 **--This value is an example. Mostly values are above 86400 seconds**
Select ActualHoursWorked =       convert(varchar,(@WorkTimeInSeconds)/3600)
            + ':' + right( '0' + convert(varchar,((@WorkTimeInSeconds) %3600)/60),2)
            + ':' + right( '0' + convert(varchar,(@WorkTimeInSeconds) %60),2)

Above code gives me what I need however the above converts the seconds in INT to varchar which is not what I need for the report. I need the output to be in datetime/time format which can give me results like '40:00:00' for the above example. I hope I am able I have provided all the information. Please help as I am struggling with this from many days.

Expected Output: 40:00:00

@AnoopH.N output should be 40:00:00. Sorry, forgot to add the expected output. Now added in the question. — ankur jain, Jan 20 '17 at 09:39

score 1 · Answer 1 · answered Jan 20 '17 at 09:29

Maybe try CONVERT style 108.

SELECT CONVERT(varchar(10),convert(varchar,(@WorkTimeInSeconds)/3600)
            + ':' + right( '0' + convert(varchar,((@WorkTimeInSeconds) %3600)/60),2)
            + ':' + right( '0' + convert(varchar,(@WorkTimeInSeconds) %60),2),108)

Convert seconds to '00:00:00' format - SQL Server

1 Answers1