Wednesday, July 1, 2009

SQL: UDF to convert the seconds into MM:SS

-- =============================================
-- Author:Sabah u din Irfan
-- Create date: July/1/2009
-- Description: A UDF to convert the seconds into MM:SS
-- Example: select [dbo].[fn_SEC2MIN](36)
-- print dbo.fn_SEC2MIN ( 36045)
-- =============================================
CREATE FUNCTION [dbo].[fn_SEC2MIN]( @Sec as int )
RETURNS VARCHAR (15)
AS
BEGIN


DECLARE @return AS VARCHAR (15)
DECLARE @i_MM as INT
DECLARE @i_SS as INT

SET @i_MM=0
SET @i_SS=0
SET @return = '00:00'

IF ( @SEC >= 60 )
BEGIN
SET @i_MM = FLOOR(@Sec / 60 )
SET @i_SS = @Sec % 60
END
ELSE
BEGIN
SET @i_SS = @Sec

END
SET @return = case len(cast( @i_MM AS Varchar)) when 1 then right('00' + cast( @i_MM AS Varchar), 2 ) else cast( @i_MM AS Varchar) end +':'+ right('00' + cast( @i_SS AS Varchar), 2 )

RETURN @return

END

SQL: UDF to convert the seconds into HH:MM:SS

-- =============================================
-- Author: Sabah u din Irfan
-- Description: A UDF to convert the seconds into HH:MM:SS
-- Example: SELECT fn_SEC2HHMMSS(65)
-- =============================================
CREATE FUNCTION [dbo].[fn_SEC2HHMMSS]( @sec as int )
RETURNS VARCHAR (15)
AS
BEGIN


RETURN
case len(convert(varchar(15),@sec/3600))
when 1
then RIGHT('00'+convert(varchar(5),@sec/3600),2)
else convert(varchar(15),@sec/3600)
end
+':'+RIGHT('0'+convert(varchar(5),@sec%3600/60),2)
+':'+RIGHT('0'+convert(varchar(5),(@sec%60)),2)


END