Convert numbers to days, hours, minutes & seconds in SQL

Mohammed Imtiyaz Jun 26, 2015

Introduction

In this tutorial we will learn how to convert Seconds to Days, Hours, Minutes & Seconds in SQL.

Suppose we have a SQL database table in which a column is saving time in seconds i.e, if we have to save 2 minutes, it will be saved as 120 seconds.

Let us say we want to retrieve time in a format, it can be any format hh:mm, hh:mm:ss and more. We can accomplish this in SQL by simply using the given below method

Here in this tutorial we are declarig a variable. We can replace it with database table column in SQL Query.

SQL Query

declare @seconds as int = 86200 -- Time in seconds

-- Now consider this number as time in seconds, let's say 86200 seconds
-- which is equivalent to 23 hours, 56 minutes and 40 seconds.
-- Based on this value, we will calculate the time in different formats.

-- Format 1: Hour(s):Minute(s)
select cast(@seconds/60/60%24 as varchar) +':'+ cast(@seconds/60%60 as varchar) 'Hours:Minutes'

-- Format 2: Hour(s):Minute(s):Second(s)
select cast(@seconds/60/60%24 as varchar) +':'+ cast(@seconds/60%60 as varchar) +':'+ cast(@seconds%60 as varchar) 'Hours:Minutes:Seconds'

-- Format 3: Day(s):Hour(s):Minute(s):Second(s)
select cast(@seconds/60/60/24 as varchar) +':'+ cast(@seconds/60/60%60 as varchar) +':'+ cast(@seconds/60%60 as varchar) +':'+ cast(@seconds%60 as varchar) 'Day(s):Hours:Minutes:Seconds'

The output for the above query will be as given below.

Convert Seconds to Days, Hours, Minutes & Seconds in SQL