Converting Seconds to Minutes in SQL Server

Before you start trying to figure out how to convert seconds into minutes in SQL server you need to be familiar with how time is structured. This is not too difficult as we all probably know how to tell the time. Just in case, it is first important to note that there are 60 seconds in every minute. Likewise there are sixty minutes in every hour and 24 hours in every day.

It may not be important to know how many minutes make an hours since our task is to convert seconds to minutes in SQL Server. Its good to note that, when structuring dates, hours, minutes and seconds will be involved.

With that said, let us get started.

To convert seconds into minutes we are going to use the SQL Server function CONVERT(). This function is a function for converting data into a new data type. It has the following syntax.

CONVERT(data_type(length), data_to_be_converted, style)

Where data_type(length) is the target data type with an optional length in braces, data_to_be_converted is the value you want to convert. Finally the style is a code represents the output format for the date/time. Valid style codes are as follows with their value in quotes:

100 or 0 "mon dd yyyy hh:miAM (or PM)"

101 "mm/dd/yy"

102 "yy.mm.dd"

103 "dd/mm/yy"

104 "dd.mm.yy"

105 "dd-mm-yy"

106 "dd mon yy"

107 "Mon dd, yy"

108 "hh:mm:ss"

109 or 9 "mon dd yyyy hh:mi:ss:mmmAM (or PM)"

110 "mm-dd-yy"

111 "yy/mm/dd"

112 "yymmdd"

113 or 13 "dd mon yyyy hh:mm:ss:mmm(24h)"

114 "hh:mi:ss:mmm(24h)"

120 or 20 "yyyy-mm-dd hh:mi:ss(24h)"

121 or 21 "yyyy-mm-dd hh:mi:ss.mmm(24h)"

126 "yyyy-mm-ddThh:mm:ss.mmm(no spaces)"

130 "dd mon yyyy hh:mi:ss:mmmAM"

131 "dd/mm/yy hh:mi:ss:mmmAM"

To use this function we are going to also implement the SQL Server DATEADD function described here. We are going to use it in two ways as there will be some unexpected behavior if the seconds add up to be more than twenty four hours. The simple syntax would be.

SELECT CONVERT(char(8), DATEADD(second, field_name, '0:00:00'), 108)

where field_name is the column that has the seconds stored in the database. From our style list above the code 108 gives us this format "hh:mm:ss". assuming this gives us an output of "10:08:05". If the total time is more than 24 hours this will be the output "10:8:5".

To solve this problem we will need to pad the minutes and seconds and fill them with leading zeros. The following is the actual example.

SELECT CONVERT(varchar(6), field_name/3600) + ':' +

RIGHT('0' + CONVERT(varchar(2), (field_name % 3600) / 60), 2)+ ':' +

RIGHT('0' + CONVERT(varchar(2), field_name % 60), 2)

Now if you strictly want to convert seconds to minutes and forget about hours, you can use these last two simple options. The first is to just convert seconds to minutes by dividing by 60 then discarding any left over seconds as shown here.

SELECT (field_name/60)

To preserve the left over seconds you would do something like this

SELECT (field_name/60) + ':' + (field_name%60)

The percent sign in the example above is a modulus which returns the remainder after dividing by 60.

You can take a look at this other SQL Server WITH function and be sure to read on the SQL Server DATEADD function too.