-- =============================================
-- Author: <Michael Ciurescu>
-- Create date: <20081105>
-- Description: Format a date the way you want for English and French, see examples below
-- 1 / NULL - for English
-- 2 - for French
-- =============================================
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)
SELECT dbo.fnFormatDate(GetDate(), 'MMMM DDDD DD, YYYY HH:NN:SS AMPM', 2)
SELECT dbo.fnFormatDate(GetDate(), 'YYYYMMDD HHNNSS', NULL)
SELECT dbo.fnFormatDate(GetDate(), 'MM-DD-YY HH:NN:SS', NULL)
SELECT dbo.fnFormatDate(GetDate(), 'M-D-YY H:N:S', NULL)
SELECT dbo.fnFormatDate(GetDate(), 'M-D-YY H:N:S:MS', NULL)
SELECT dbo.fnFormatDate('nov 1, 2008 15:01:02', 'MMMM DDDD D, YYYY H:N:S AMPM', 1)
SELECT dbo.fnFormatDate('nov 1, 2008 15:01:02', 'MMMM DDDD D, YYYY H:N:S AMPM', 2)
SELECT dbo.fnFormatDate('nov 1, 2008 15:01:02', 'MMMM DDDD DD, YYYY HH:NN:SS AMPM', NULL)
SELECT dbo.fnFormatDate('nov 1, 2008 5:01:02', 'MMM DD, YYYY HH:NN:SS AMPM', NULL)
*/
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
