FormatDate 2 All languages

,

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.

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

Rate

Share

Share

Rate