CREATE FUNCTION dbo.DateTimeFormats
(
    @dt DATETIME,
    @format VARCHAR(16)
)
RETURNS VARCHAR(64)
AS
BEGIN
    DECLARE @dtVC VARCHAR(64)
    SELECT @dtVC = CASE @format

    WHEN 'DDMMMMYYYY' THEN

         CAST(DAY(@dt) AS VARCHAR(2))
        + SPACE(1) + DATENAME(m, @dt)
         + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))



    WHEN 'LongDateAndTime' THEN

        DATENAME(dw, @dt)
        + ',' + SPACE(1) + DATENAME(m, @dt)
        + SPACE(1) + CAST(DAY(@dt) AS VARCHAR(2))
        + ',' + SPACE(1) + CAST(YEAR(@dt) AS CHAR(4))
        + SPACE(1) + RIGHT(CONVERT(CHAR(20),
        @dt - CONVERT(DATETIME, CONVERT(CHAR(8),
        @dt, 112)), 22), 11)

   



    ELSE

        'Invalid format specified'

    END
    RETURN @dtVC
END








-------------EXECUTION------------



DECLARE @now DATETIME
SET @now = GETDATE()

PRINT dbo.DateTimeFormats(@now, 'DDMMMMYYYY')
PRINT dbo.DateTimeFormats(@now, 'LongDateAndTime')