A UDF to convert the seconds into MM:SS

A UDF to convert the seconds into MM:SS Format:

Here is the Code:


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

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 =right('00' + cast( @i_MM AS Varchar), 2 )+':'+ right('00' + cast( @i_SS AS Varchar), 2 )

RETURN @return
END

Here is the test query for the UDF:

print dbo.fn_SEC2MIN ( 120)

Phone Number Formatting UDF

The following UDF will give you the Formatted Phone Number:

CREATE FUNCTION [dbo].[fn_PhoneNoFormat]( @Phone as char(10) )
RETURNS VARCHAR (15)
AS
BEGIN

Return '('+substring(@phone,1,3)+') '+substring(@phone,4,3)+'-'+substring(@phone,7,4)

END

Here is the Test Query:

print dbo.fn_PhoneNoFormat( '1234567890')

UDF to Get the DD Part of the last Date of Month

-- =============================================
A UDF to get the DD part of the last date of the month according to the date passed into its parameter
-- =============================================
CREATE FUNCTION [dbo].[fn_DD_OF_Last_Date_OF_Month]( @myDate as datetime)
RETURNS INT
AS
BEGIN

DECLARE @return AS INT
SET @return =0
Declare @myDate2 datetime


SET @mydate2=dateadd(m,1,@myDate)-day(dateadd(m,1,@myDate))

SET @return= cast (datepart(dd,@myDate2) as int)

RETURN @return
END

The following query is to test the UDF:

select [dbo].[fn_DD_OF_Last_Date_OF_Month]( '03/13/2005')