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.
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.