Format Date Function

IF OBJECT_ID(N'dbo.fnFormatDate') IS NOT NULL
	DROP FUNCTION dbo.fnFormatDate
GO

CREATE FUNCTION dbo.fnFormatDate (
	@MyDate DATETIME
	,@Format NVARCHAR(50)
	,@LANGUAGE INT = NULL
	)
RETURNS NVARCHAR(255)
AS
BEGIN
	/*
        SELECT dbo.fnFormatDate(GetDate(), 'MMMM DDDD DD, YYYY HH:NN:SS AMPM', NULL)
        --October Friday 03, 2013 00:13:28 PM

        SELECT dbo.fnFormatDate(GetDate(), 'MMMM DDDD DD, YYYY HH:NN:SS AMPM', 2)
        --octobre vendredi 03, 2013 00:14:03 PM

        SELECT dbo.fnFormatDate(GetDate(), 'YYYYMMDD HHNNSS', NULL)
        --20131003 121403

        SELECT dbo.fnFormatDate(GetDate(), 'MM-DD-YY HH:NN:SS', NULL)
        --10-03-13 12:14:03

        SELECT dbo.fnFormatDate(GetDate(), 'M-D-YY H:N:S', NULL)
        --10-3-13 12:14:3

        SELECT dbo.fnFormatDate(GetDate(), 'M-D-YY H:N:S:MS', NULL)
        --10-3-13 12:15:4:407

        SELECT dbo.fnFormatDate('nov 1, 2008 15:01:02', 'MMMM DDDD D, YYYY H:N:S AMPM', 1)
        --November Sunday 1, 2008 3:1:2 PM

        SELECT dbo.fnFormatDate('nov 1, 2008 15:01:02', 'MMMM DDDD D, YYYY H:N:S AMPM', 2)
        --novembre dimanche 1, 2008 3:1:2 PM

        SELECT dbo.fnFormatDate('nov 1, 2008 15:01:02', 'MMMM DDDD DD, YYYY HH:NN:SS AMPM', NULL)
        --November Sunday 01, 2008 03:01:02 PM

        SELECT dbo.fnFormatDate('nov 1, 2008 5:01:02', 'MMM DD, YYYY HH:NN:SS AMPM', NULL)
        --Nov 01, 2008 05:01:02 AM
    */
	DECLARE @RetStr NVARCHAR(255)
	DECLARE @tMonth TABLE (
		MonthID INT
		,MonthStr NVARCHAR(20)
		,LANGUAGE INT
		)
	DECLARE @tWeekDay TABLE (
		WeekDayID INT
		,WeekDayStr NVARCHAR(20)
		,LANGUAGE INT
		)
	DECLARE @Year INT
	DECLARE @Month INT
	DECLARE @WeekDay INT
	DECLARE @Day INT
	DECLARE @Hour INT
	DECLARE @Minute INT
	DECLARE @Second INT
	DECLARE @MS INT

	IF @LANGUAGE < 1
		OR @LANGUAGE > 2
		RETURN NULL

	INSERT INTO @tWeekDay
	VALUES (
		1
		,'Monday'
		,1
		)

	INSERT INTO @tWeekDay
	VALUES (
		2
		,'Tuesday'
		,1
		)

	INSERT INTO @tWeekDay
	VALUES (
		3
		,'Wednesday'
		,1
		)

	INSERT INTO @tWeekDay
	VALUES (
		4
		,'Thursday'
		,1
		)

	INSERT INTO @tWeekDay
	VALUES (
		5
		,'Friday'
		,1
		)

	INSERT INTO @tWeekDay
	VALUES (
		6
		,'Saturday'
		,1
		)

	INSERT INTO @tWeekDay
	VALUES (
		7
		,'Sunday'
		,1
		)

	INSERT INTO @tMonth
	VALUES (
		1
		,'January'
		,1
		)

	INSERT INTO @tMonth
	VALUES (
		2
		,'February'
		,1
		)

	INSERT INTO @tMonth
	VALUES (
		3
		,'March'
		,1
		)

	INSERT INTO @tMonth
	VALUES (
		4
		,'April'
		,1
		)

	INSERT INTO @tMonth
	VALUES (
		5
		,'May'
		,1
		)

	INSERT INTO @tMonth
	VALUES (
		6
		,'June'
		,1
		)

	INSERT INTO @tMonth
	VALUES (
		7
		,'July'
		,1
		)

	INSERT INTO @tMonth
	VALUES (
		8
		,'August'
		,1
		)

	INSERT INTO @tMonth
	VALUES (
		9
		,'September'
		,1
		)

	INSERT INTO @tMonth
	VALUES (
		10
		,'October'
		,1
		)

	INSERT INTO @tMonth
	VALUES (
		11
		,'November'
		,1
		)

	INSERT INTO @tMonth
	VALUES (
		12
		,'December'
		,1
		)

	INSERT INTO @tWeekDay
	VALUES (
		1
		,'lundi'
		,2
		)

	INSERT INTO @tWeekDay
	VALUES (
		2
		,'mardi'
		,2
		)

	INSERT INTO @tWeekDay
	VALUES (
		3
		,'mercredi'
		,2
		)

	INSERT INTO @tWeekDay
	VALUES (
		4
		,'jeudi'
		,2
		)

	INSERT INTO @tWeekDay
	VALUES (
		5
		,'vendredi'
		,2
		)

	INSERT INTO @tWeekDay
	VALUES (
		6
		,'samedi'
		,2
		)

	INSERT INTO @tWeekDay
	VALUES (
		7
		,'dimanche'
		,2
		)

	INSERT INTO @tMonth
	VALUES (
		1
		,'janvier'
		,2
		)

	INSERT INTO @tMonth
	VALUES (
		2
		,'fevrier'
		,2
		)

	INSERT INTO @tMonth
	VALUES (
		3
		,'mars'
		,2
		)

	INSERT INTO @tMonth
	VALUES (
		4
		,'avril'
		,2
		)

	INSERT INTO @tMonth
	VALUES (
		5
		,'mai'
		,2
		)

	INSERT INTO @tMonth
	VALUES (
		6
		,'juin'
		,2
		)

	INSERT INTO @tMonth
	VALUES (
		7
		,'juillet'
		,2
		)

	INSERT INTO @tMonth
	VALUES (
		8
		,'aout'
		,2
		)

	INSERT INTO @tMonth
	VALUES (
		9
		,'septembre'
		,2
		)

	INSERT INTO @tMonth
	VALUES (
		10
		,'octobre'
		,2
		)

	INSERT INTO @tMonth
	VALUES (
		11
		,'novembre'
		,2
		)

	INSERT INTO @tMonth
	VALUES (
		12
		,'decembre'
		,2
		)

	SET @RetStr = @Format
	SET @Year = DATEPART(year, @MyDate)
	SET @Month = DATEPART(month, @MyDate)
	SET @WeekDay = DATEPART(weekday, @MyDate)
	SET @Day = DATEPART(day, @MyDate)
	SET @Hour = DATEPART(hour, @MyDate)
	SET @Minute = DATEPART(minute, @MyDate)
	SET @Second = DATEPART(second, @MyDate)
	SET @MS = DATEPART(millisecond, @MyDate)

	SELECT @RetStr = REPLACE(@RetStr, 'YYYY' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(4), @Year))

	SELECT @RetStr = REPLACE(@RetStr, 'YY' COLLATE Latin1_General_CS_AS, RIGHT(CONVERT(NVARCHAR(4), @Year), 2))

	SELECT @RetStr = REPLACE(@RetStr, ' MS' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(5), @MS))

	SELECT @RetStr = REPLACE(@RetStr, ':MS' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(5), @MS))

	SELECT @RetStr = REPLACE(@RetStr, '.MS' COLLATE Latin1_General_CS_AS, '.' + CONVERT(NVARCHAR(5), @MS))

	SELECT @RetStr = REPLACE(@RetStr, 'MS' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(5), @MS))

	SELECT @RetStr = REPLACE(@RetStr, ' M ' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Month) + ' ')

	SELECT @RetStr = REPLACE(@RetStr, ' M,' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Month) + ',')

	SELECT @RetStr = REPLACE(@RetStr, '-M-' COLLATE Latin1_General_CS_AS, '-' + CONVERT(NVARCHAR(2), @Month) + '-')

	SELECT @RetStr = REPLACE(@RetStr, ' D ' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Day) + ' ')

	SELECT @RetStr = REPLACE(@RetStr, ' D,' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Day) + ',')

	SELECT @RetStr = REPLACE(@RetStr, '-D-' COLLATE Latin1_General_CS_AS, '-' + CONVERT(NVARCHAR(2), @Day) + '-')

	SELECT @RetStr = REPLACE(@RetStr, ':N:' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Minute) + ':')

	SELECT @RetStr = REPLACE(@RetStr, ':S ' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Second) + ' ')

	SELECT @RetStr = REPLACE(@RetStr, 'NN' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Minute), 2))

	SELECT @RetStr = REPLACE(@RetStr, 'SS' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Second), 2))

	SELECT @RetStr = REPLACE(@RetStr, ':S' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Second))

	SELECT @RetStr = REPLACE(@RetStr, 'MMMM' COLLATE Latin1_General_CS_AS, m.MonthStr)
	FROM @tMonth AS m
	WHERE m.MonthID = @Month
		AND m.LANGUAGE = ISNULL(@LANGUAGE, 1)

	SELECT @RetStr = REPLACE(@RetStr, 'MMM' COLLATE Latin1_General_CS_AS, LEFT(m.MonthStr, 3))
	FROM @tMonth AS m
	WHERE m.MonthID = @Month
		AND m.LANGUAGE = ISNULL(@LANGUAGE, 1)

	SELECT @RetStr = REPLACE(@RetStr, 'DDDD' COLLATE Latin1_General_CS_AS, w.WeekDayStr)
	FROM @tWeekDay AS w
	WHERE w.WeekDayID = @WeekDay
		AND w.LANGUAGE = ISNULL(@LANGUAGE, 1)

	SELECT @RetStr = REPLACE(@RetStr, 'DDD' COLLATE Latin1_General_CS_AS, LEFT(w.WeekDayStr, 3))
	FROM @tWeekDay AS w
	WHERE w.WeekDayID = @WeekDay
		AND w.LANGUAGE = ISNULL(@LANGUAGE, 1)

	SELECT @RetStr = REPLACE(@RetStr, 'MM' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Month), 2))

	SELECT @RetStr = REPLACE(@RetStr, 'M-' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Month) + '-')

	SELECT @RetStr = REPLACE(@RetStr, 'DD' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Day), 2))

	IF CHARINDEX('AMPM', @RetStr) > 0
	BEGIN
		IF @Hour < 12
		BEGIN
			SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour), 2))

			SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour))

			SELECT @RetStr = REPLACE(@RetStr, 'AMPM' COLLATE Latin1_General_CS_AS, 'AM')
		END
		ELSE
		BEGIN
			SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour - 12), 2))

			SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour - 12))

			SELECT @RetStr = REPLACE(@RetStr, 'AMPM' COLLATE Latin1_General_CS_AS, 'PM')
		END
	END
	ELSE
	BEGIN
		SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour), 2))

		SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour))
	END

	RETURN @RetStr
END

Source: http://www.vbforums.com/showthread.php?545861-SQL-Server-Date-Format-Function-in-English-and-French