FormatDate 2 All languages

--------------------------------------------------------------------------------
-- Format: Jourdddd: nom complet du jour
--ddd: nom abrégé jour 
--dd: numéro de jour sur 2 chiffres
--d: numéro de jour
--  Moismmmm: nom complet du mois
--mmm: nom abrégé du mois
--mm: numéro du mois sur 2 chiffres
--m: numéro du mois
--  Annéeyyyy: 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
@formatvarchar(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,
@paternAS 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
@MonthNamesvarchar(2000),
@IndexMonthint,
@posint
-- Récupère
SELECT @MonthNames = months 
FROMmaster.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
@MonthNamesvarchar(2000),
@IndexMonthint,
@posint
-- Récupère
SELECT @MonthNames = shortmonths 
FROMmaster.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