Minutes to time in SQL Server

Question

I've created a function for converting minutes (smallint) to time (varchar(5)), like 58 -> 00:58.

set QUOTED_IDENTIFIER ON

GO
Create FUNCTION [dbo].[IntToMinutes]
(
    @m smallint
)
RETURNS nvarchar(5)
AS
BEGIN
    DECLARE @c nvarchar(5)
     SET @c = CAST((@m / 60) as varchar(2)) + ':' + CAST((@m % 60) as varchar(2))
     RETURN @c
END

The problem is when there are less than 10 minutes in time, like 9. The result of this function is 0:9. I want that the format is 00:09.

How can I do that?

Answer 1

Pad the two (sub)strings with leading zeroes, then take the rightmost two characters returned:

CREATE FUNCTION [dbo].[IntToMinutes] 
( 
    @m smallint  
)   
RETURNS nvarchar(5) 
AS   
BEGIN 
    DECLARE @c nvarchar(5) 

    SET @c = RIGHT('0' + CAST((@m / 60) as varchar(2)), 2)
     + ':'
     + RIGHT('0' + CAST((@m % 60) as varchar(2)), 2)

     RETURN @c 
END

Note, of course, that this doesn't work so well once you get past 5999 minutes. If that's a problem, just make things a bit bigger/longer.

Answer 2

declare @m smallint
declare @d varchar(10)

set @m = 9

DECLARE @c nvarchar(5)
SET @c = CAST((@m / 60) as varchar(2)) + ':' + CAST((@m % 60) as varchar(2))
set @d = Convert(varchar,cast(@c as datetime), 108)
set @c = LEFT(@d,5)
select @c

This works only for numbers between 0 and 1439, but it's fine for 24 hours. You can skip variable declaration @d and do that inline.

Answer 3

Create FUNCTION [dbo].[IntToMinutes]
(
    @m smallint 
)  
RETURNS nvarchar(5)
AS  
BEGIN
    DECLARE @c datetime
    select @c = dateadd(mi,@m,'00:00')       
    RETURN convert(nvarchar(5), @c, 108)   
END