How to Convert Seconds to HH:MM:SS

If you have a column that contains time in seconds and want to convert it to an hour : minute: seconds format, use this query. This query also takes care of displaying hours > 23.

DECLARE @SecondsToConvert int
SET @SecondsToConvert = 10000

— Declare variables
DECLARE @Hours int
DECLARE @Minutes int
DECLARE @Seconds int
DECLARE @Time datetime

— Set the calculations for hour, minute and second
SET @Hours = @SecondsToConvert/3600
SET @Minutes = (@SecondsToConvert % 3600) / 60
SET @Seconds = @SecondsToConvert % 60

— Store the datetime information retrieved in the @Time variable
SET @Time = (SELECT
RTRIM(CONVERT(char(8), @Hours) ) + ‘:’ +
CONVERT(char(2), @Minutes) + ‘:’ +
CONVERT(char(2), @Seconds));

— Display the @Time variable in the format of HH:MM:SS
SELECT CONVERT(varchar(8),CONVERT(datetime,@Time),108)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s