seconds to minutes? 1

[olemma]

Is there any sql funtions that converts seconds to minutes? I need someting like 245 seconds = 04:05 minutes. I can probably write a funtion to do this but I was wondering if some existed already.
Thanks!

 

[fluteplr]

Not built in.

 

[olemma]

Thanks. I wrote this funtion that seems to work, but now how do i call it from select statement? I want return a duration field which is in seconds in minutes.

CREATE function f_sec2min (@p_seconds as numeric)
returns char(20)
as
begin
declare @min char(20)
set @min=case len(cast( floor(@p_seconds/60) as char(2))) when 1 then ltrim( '0'+cast( floor(@p_seconds/60) as char(1)) ) else cast( floor(@p_seconds/60) as char(2)) end +':'+
case len(cast( ceiling(((@p_seconds/60)- floor (@p_seconds/60)) * 60) as char(2) ) )when 1 then '0'+cast( ceiling(((@p_seconds/60)- floor (@p_seconds/60)) * 60) as char(1) ) else cast( ceiling(((@p_seconds/60)- floor (@p_seconds/60)) * 60) as char(2) ) end
return @min
end

 

[tlbroadbent]

Here is a much simpler script to perform the conversion.

Declare @sec int
Set @sec=245

--convert seconds to hours, minutes and seconds
Select convert(char(8), dateadd(s, @sec, '1900-01-01'), 8)

--convert seconds to minutes and seconds
Select Cast(Right(convert(char(8), dateadd(s, @sec, '1900-01-01'), 8), 5) As char(5)) If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[olemma]

Ty! much better!