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

Jun 26, 2015 By Mohammed Imtiyaz

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'

Output

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

Output


Post your comment