Efficient way to convert second to minute and seconds in sql server 2005

Question

Suppose I have 90 seconds. If I want to display the result in terms of minutes and second, I do it by using

select Time= '0' + CAST( 90/60 as varchar(2)) + ':' +  CAST( 90%60 as varchar(2)) 

The output is

Time
01:30

I have appended 0(zero) because if you do a select getdate() the output will be

yyyy-mm-dd hh:mm:ss:ms

What is the standard way and recommended practice to do such a conversion?

Thanks

Answer 1

With hours:

SELECT CONVERT(CHAR(8),DATEADD(second,90,0),108)
00:01:30

Ignoring hours:

SELECT RIGHT(CONVERT(CHAR(8),DATEADD(second,90,0),108),5)
01:30

Answer 2

One of the first things I do on a fresh SQL database is add a Timespan function similar to this one (although I tend to include days and milliseconds as well):

CREATE FUNCTION dbo.TimeSpan
(
    @Hours int,
    @Minutes int,
    @Seconds int
)
RETURNS datetime
AS BEGIN
    RETURN DATEADD(SS, @Hours * 3600 + @Minutes * 60 + @Seconds, 0)
END

Then you can format this however you want:

SELECT SUBSTRING(CONVERT(char(8), dbo.TimeSpan(0, 0, 90), 108), 4, 5)

It might look more complicated at first, but the ability to reuse the TimeSpan function comes in very handy over time. For me it feels like a hack to always be writing DATEADD calls against 0 or '1753-01-01'.