Return formatted datetime as a string

,

Pass a datetime value and receive back a formatted string. Much like the Format$ function in Visual Basic.

I put this together to provide a date string to add to filename. Also handy when you are creating text files that require datetime data to be output in weird character formats like yyddmm or yyyymm.

eg: fnDate2Char(GetDate(), 'dmy', 1, 1) will return 26022002 for the 26th Feb 2002.

IF EXISTS (SELECT * FROM dbo.sysobjects 
			WHERE id = object_id(N'[dbo].[fnDate2Char]') 
				AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION dbo.fnDate2Char
GO

/****** Object:  User Defined Function dbo.fnDate2Char    Script Date: 15/02/2002 14:56:42 ******/
CREATE FUNCTION dbo.fnDate2Char (
	@dtDate datetime
	, @vcFmt varchar(6)
	, @iPad int = 1
	, @i4dy int = 1
)  
RETURNS varchar(30) AS

BEGIN -- function
	/*****************************************************************************************
	SQL SERVER OBJECT NAME:  
		dbo.fnDate2Char
	AUTHOR: 
		Phillip Carter
	DATE WRITTEN:
		15 Feb 2002
	PURPOSE:
		Return formatted date string
	ACTIONS:
		Accept datetime variable and format string
		Break passed datetime value into seperate components
		Move through format string character by character
		adding date portion indicated by character.
	INPUTS:
		@dtDate datetime - the date to re-format
		@vcFmt varchar(6) - datetime format string
		@iPad int - whether to pad out numbers with leading zeros
		@i4dy int - whether use 2 or 4 digit years
	USAGE:  
		fnDate2Char(GetDate(), 'dmy', 1, 1) will return 26022002 
		for the 26th Feb 2002

	OUTPUTS:
		@vcReturn varchar(30) - the formatted date string to return
	
	MODIFICATION HISTORY
	DATE		PERSON		REASON
	----		------		-----------------------------------------
	dd/mm/yyyy	Who		what, why

	*****************************************************************************************/
	-- declare local variables
	DECLARE @vcReturn varchar(30) -- return string
	DECLARE @vcDay varchar(2) -- day portion of datetime
	DECLARE @vcMonth varchar(2) -- month portion of datetime
	DECLARE @vcYear varchar(4) -- year portion of datetime
	DECLARE @vcHour varchar(2) -- hour portion of datetime
	DECLARE @vcMin varchar(2) -- minute portion of datetime
	DECLARE @vcSec varchar(2) -- second portion of datetime
	DECLARE @iLen int -- length of datetime format string
	DECLARE @iPos int -- current position in datetime format string
	DECLARE @cChr char(1) -- current character in datetime format string
	DECLARE @iValid int -- flag indicating valid format string
	DECLARE @cValidChr char(6) -- list of vaild format characters
	DECLARE @iFound int -- 

	-- initialise variables
	SET @vcReturn = ''
	SET @iValid = 1
	SET @cValidChr = 'dmyhns'
	SET @iPos = 0
	SET @iLen = DATALENGTH(@vcFmt)

	-- validate characters in format string
	WHILE @iPos < @iLen
	BEGIN -- while
		-- increment character position
		SET @iPos = @iPos + 1
		-- get character from format string
		SET @cChr = UPPER(SUBSTRING(@vcFmt, @iPos, 1))
		SET @iFound = CHARINDEX(@cChr, @cValidChr)
		IF @iFound = 0
			SET @iValid = 0
	END
	SET @iPos = 0
	SET @iLen = DATALENGTH(@cValidChr)
	-- check for duplicate characters in format string
	WHILE @iPos < @iLen
	BEGIN -- while
		-- increment character position
		SET @iPos = @iPos + 1
		-- get character from format string
		SET @cChr = UPPER(SUBSTRING(@cValidChr, @iPos, 1))
		SET @iFound = CHARINDEX(@cChr, @vcFmt)
		WHILE (@iFound > 0) OR ((@iFound + 1) > DATALENGTH(@vcFmt))
		BEGIN
			SET @iFound = CHARINDEX(@cChr, @vcFmt, @iFound + 1)
			IF @iFound > 0
				SET @iValid = 0
		END
	END
	-- if format string is valid return formatted date string
	IF @iValid = 1
	BEGIN
		SET @iLen = DATALENGTH(@vcFmt)
		-- break datetime value into seperate components
		SET @vcDay = DATEPART(dd, @dtDate)
		SET @vcMonth = DATEPART(mm, @dtDate)
		SET @vcYear = DATEPART(yy, @dtDate)
		SET @vcHour = DATEPART(hh, @dtDate)
		SET @vcMin = DATEPART(mi, @dtDate)
		SET @vcSec = DATEPART(ss, @dtDate)
		-- pad out numbers with leading zeros if required
		IF @iPad = 1
		BEGIN
			SET @vcDay = RIGHT('00' + @vcDay, 2)
			SET @vcMonth = RIGHT('00' + @vcMonth, 2)
			SET @vcYear = RIGHT('0000' + @vcYear, 4)
			SET @vcHour = RIGHT('00' + @vcHour, 2)
			SET @vcMin = RIGHT('00' + @vcMin, 2)
			SET @vcSec = RIGHT('00' + @vcSec, 2)
		END
		-- set 2 digit year
		IF @i4dy = 0
			SET @vcYear = RIGHT(@vcYear, 2)		
		SET @iPos = 0
		-- add date portions in order specified in format string
		WHILE @iPos < @iLen
		BEGIN -- while
			-- increment character position
			SET @iPos = @iPos + 1
			-- get character from format string
			SET @cChr = UPPER(SUBSTRING(@vcFmt, @iPos, 1))
			-- add datetime portion indicated by format character
			SELECT @vcReturn = @vcReturn + 
				CASE @cChr
					WHEN 'D' THEN @vcDay
					WHEN 'M' THEN @vcMonth
					WHEN 'Y' THEN @vcYear
					WHEN 'H' THEN @vcHour
					WHEN 'N' THEN @vcMin
					WHEN 'S' THEN @vcSec
				END -- case
		END -- while
	END
	ELSE
	BEGIN
		SET @vcReturn = 'Format Error'
	END
	RETURN (@vcReturn)
END -- function

Rate

Share

Share

Rate