Format Date Function

Posted on 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.
Code MSSQL MSSQL - Functions SQL

Related Posts

Debian/Ubuntu LAMP Setup

Posted on

The following code is for Debian 9 Stretch but should work on older Debian with minor changes Originally Posted on January 18, 2018Last Updated on January 31, 2019 All information on this site is shared with the intention to help. Before any source code or program is ran on a…

Read More

DigiCert – OpenSSL Certificate Conversion

Posted on

I work with digicert® to get certificates. The way we get certificates sometimes requires me to convert the certificates, below is the process I use to get the formats for what I may need. This script assumes you have a directory with only the .csr file and the .zip file…

Read More