Formating a TIME string in SQL 1

[LoveToCode]

Hello,

I have a table that record the length of an MP3 file in a string that looks like this:

Length: 369

I'd like to format that into a time string, like this:

3:69

the only thing is, notice how the time is 3:69... which does not make sense. I think the length being returned is in seconds... like three hundred and 69 seconds? I'm not quite sure. Does anyone have experience with this and call help me format this corectly?

Thanks.

 

[AlexCuse]

Here is a function:

alter function SecToMin(@sec integer)
returns varchar(5)

as

begin

declare @min integer, @time varchar(5)
select @min = @sec / 60
select @sec = @sec - (@min * 60)
select @time = convert(varchar(2), @min) + ':' + right(convert(char(2), '00') + convert(varchar(2), @sec), 2)

return @time

end

Here is how you will call it in your query:

select dbo.SecToMin([Column Containing Seconds])

This might be something best handled on the front end application though.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[LoveToCode]

Hey that works perfectly!! Thanks so much.

 

[AlexCuse]

Glad it worked for you!

In case you have any very long songs, I wrote one that handles hours as well. I submitted it as an FAQ, but until then here is that function if you are interested:

create function SecToTime(@sec integer)
returns varchar(12)

as

begin

declare @min integer, @hour integer, @time varchar(12), @dz char(2)

set @dz = '00'

If (@sec / 60) >= 60
Begin
    set @hour = (@sec / 3600)
    set @min = (@sec / 60) - (@hour * 60)
    set @sec = @sec - ((@min + (@hour * 60)) * 60)
    set @time = convert(varchar(12), @hour) + ':' + convert(char(2), right(@dz + convert(varchar(2), @min), 2))
        + ':' + right(@dz + convert(varchar(2), @sec), 2)
End
Else
Begin
    set @min = @sec / 60
    set @sec = @sec - (@min * 60)
    set @time = convert(varchar(2), @min) + ':' + right(@dz + convert(varchar(2), @sec), 2)
End

return @time

end

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Whoops, I left some extra converts in there. Here is a link to the FAQ, it has the correct funtion.

faq183-6519

Ignorance of certain subjects is a great part of wisdom