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
DATEPERSONREASON
---------------------------------------------------
dd/mm/yyyyWhowhat, 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