Format Date Function

Posted on July 31, 2013October 26, 2015 By David Kittell

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

Originally Posted on July 31, 2013
Last Updated on October 26, 2015

All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.

Related

Code MSSQL MSSQL - Functions SQL