This scipt is inspired by FormatDate - Mimics the VB Format routine for date. But this script work actually only whith a english Server. I improved this by a language independant. I use 2 others functions to retrieve the Long and short month name.
FormatDate 2 All languages
--------------------------------------------------------------------------------
-- Format : Jour dddd : nom complet du jour
-- ddd : nom abrégé jour
-- dd : numéro de jour sur 2 chiffres
-- d : numéro de jour
-- Mois mmmm : nom complet du mois
-- mmm : nom abrégé du mois
-- mm : numéro du mois sur 2 chiffres
-- m : numéro du mois
-- Année yyyy : année sur 4 chiffres
-- yy : année sur 2 chiffres
--------------------------------------------------------------------------------
-- Exemple : dddd, d mmmm yyyy --> Dimanche, 14 juillet 2003
-- ddmmyyyy --> 14072003
-- d-m-yy --> 14-7-03
-- dd/mm/yyyy --> 14/07/2003
--------------------------------------------------------------------------------
CREATE FUNCTION dbo.uf_FormatDate
(
@date datetime, -- Date à formater
@format varchar(50) -- Format de date
)
RETURNS VARCHAR(50)
AS
BEGIN
--------------------------------------------------------------------------------
-- Null ?
--------------------------------------------------------------------------------
IF @date IS NULL RETURN NULL
IF @format IS NULL RETURN NULL
--------------------------------------------------------------------------------
-- Variable
--------------------------------------------------------------------------------
DECLARE
@pos AS INTEGER,
@patern AS VARCHAR(4)
--------------------------------------------------------------------------------
-- Remplace les paramètres
--------------------------------------------------------------------------------
SET @format = REPLACE(@format, 'yyyy', REPLICATE(CHAR(9),4))
SET @format = REPLACE(@format, 'yy', REPLICATE(CHAR(9),2))
SET @format = REPLACE(@format, 'mmmm', REPLICATE(CHAR(10),4))
SET @format = REPLACE(@format, 'mmm', REPLICATE(CHAR(10),3))
SET @format = REPLACE(@format, 'mm', REPLICATE(CHAR(10), 2))
SET @format = REPLACE(@format, 'm', REPLICATE(CHAR(10),1))
SET @format = REPLACE(@format, 'dddd', REPLICATE(CHAR(11),4))
SET @format = REPLACE(@format, 'ddd', REPLICATE(CHAR(11),3))
SET @format = REPLACE(@format, 'dd', REPLICATE(CHAR(11), 2))
SET @format = REPLACE(@format, 'd', REPLICATE(CHAR(11),1))
--------------------------------------------------------------------------------
-- Remplace l'année
--------------------------------------------------------------------------------
SET @patern = REPLICATE(CHAR(9),4)
SET @pos = CHARINDEX(@patern, @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 4, DATENAME(yyyy, @date))
SET @pos = CHARINDEX(@patern, @format)
END
SET @patern = REPLICATE(CHAR(9),2)
SET @pos = CHARINDEX(@patern, @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 2, RIGHT(DATENAME(yyyy, @date) ,2))
SET @pos = CHARINDEX(@patern, @format)
END
--------------------------------------------------------------------------------
-- Remplace le mois
--------------------------------------------------------------------------------
SET @patern = REPLICATE(CHAR(10),4)
SET @pos = CHARINDEX(@patern, @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 4, dbo.uf_MonthName(month(@date)))
SET @pos = CHARINDEX(@patern, @format)
END
SET @patern = REPLICATE(CHAR(10),3)
SET @pos = CHARINDEX(@patern, @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 3, dbo.uf_ShortMonthName(month(@date)))
SET @pos = CHARINDEX(@patern, @format)
END
SET @patern = REPLICATE(CHAR(10),2)
SET @pos = CHARINDEX(@patern, @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 2, RIGHT(('0' + CAST(DATEPART(month, @date) AS VARCHAR(2))), 2))
SET @pos = CHARINDEX(@patern, @format)
END
SET @patern = REPLICATE(CHAR(10),1)
SET @pos = CHARINDEX(@patern, @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 1, CAST(DATEPART(month, @date) AS VARCHAR(2)))
SET @pos = CHARINDEX(@patern, @format)
END
--------------------------------------------------------------------------------
-- Replace le jour
--------------------------------------------------------------------------------
SET @patern = REPLICATE(CHAR(11),4)
SET @pos = CHARINDEX(@patern, @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 4, DATENAME(weekday, @date))
SET @pos = CHARINDEX(@patern, @format)
END
SET @patern = REPLICATE(CHAR(11),3)
SET @pos = CHARINDEX(@patern, @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 3, LEFT(DATENAME(weekday, @date), 3))
SET @pos = CHARINDEX(@patern, @format)
END
SET @patern = REPLICATE(CHAR(11),2)
SET @pos = CHARINDEX(@patern, @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 2, RIGHT(('0' + DATENAME(day, @date)), 2))
SET @pos = CHARINDEX(@patern, @format)
END
SET @patern = REPLICATE(CHAR(11),1)
SET @pos = CHARINDEX(@patern, @format)
WHILE @pos > 0
BEGIN
SET @format = STUFF(@format, @pos, 1, CAST(DATEPART(day, @date) AS VARCHAR(2)))
SET @pos = CHARINDEX(@patern, @format)
END
-- Vide ?
IF @format = '//'
BEGIN
SET @format = ''
END
-- Renvoie
RETURN @format
END
--------------------------------------------------------------------------------
CREATE FUNCTION dbo.uf_MonthName
(
@month int
)
RETURNS VARCHAR(15)
AS
BEGIN
-- Variable
DECLARE
@MonthNames varchar(2000),
@IndexMonth int,
@pos int
-- Récupère
SELECT @MonthNames = months
FROM master.dbo.syslanguages
WHERE name = @@language
-- Initialise
SET @IndexMonth = 1
--Boucle
WHILE @IndexMonth < @month
BEGIN
SET @pos = CHARINDEX(',', @MonthNames)
SET @MonthNames = SUBSTRING(@MonthNames, @pos + 1, 1000)
SET @IndexMonth = @IndexMonth + 1
END
-- Dernier ?
IF CHARINDEX(',', @MonthNames)> 0
SET @MonthNames = LEFT(@MonthNames, CHARINDEX(',', @MonthNames) - 1)
-- Renvoie
RETURN @MonthNames
END
--------------------------------------------------------------------------------
CREATE FUNCTION dbo.uf_ShortMonthName
(
@month int
)
RETURNS VARCHAR(15)
AS
BEGIN
-- Variable
DECLARE
@MonthNames varchar(2000),
@IndexMonth int,
@pos int
-- Récupère
SELECT @MonthNames = shortmonths
FROM master.dbo.syslanguages
WHERE name = @@language
-- Initialise
SET @IndexMonth = 1
--Boucle
WHILE @IndexMonth < @month
BEGIN
SET @pos = CHARINDEX(',', @MonthNames)
SET @MonthNames = SUBSTRING(@MonthNames, @pos + 1, 1000)
SET @IndexMonth = @IndexMonth + 1
END
-- Dernier
IF CHARINDEX(',', @MonthNames)> 0
SET @MonthNames = LEFT(@MonthNames, CHARINDEX(',', @MonthNames) - 1)
-- Renvoie
RETURN @MonthNames
END