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
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.