Formating Date/Time with your own Format String

[SlashZero]

In Access and Visual Basic this is a nice little "Format" function you can pass a date/time variable and a formating string and it returns the combined result. Like Format(Now(), "m/d/yy&quot Would return a string like "1/2/03". This function does not exist in SQL Server and I have found no easy way to format a string like this. So.... I wrote it...
You need to add both of these User-Defined Function to your DB. I hope you find this useful!

CREATE FUNCTION INSTR (@startpos int, @mainstr varchar(255), @searchstr varchar(255))
RETURNS int
AS
BEGIN
DECLARE @pos int
SET @pos = @startpos
WHILE @pos <= LEN(@mainstr)+1 - LEN(@searchstr)
BEGIN
IF SUBSTRING(@mainstr, @pos, LEN(@searchstr)) = @searchstr
BEGIN
RETURN @pos
BREAK
END
SET @pos = @pos + 1
END
RETURN 0
END

-------------------AND THE OTHER-------------------------

CREATE FUNCTION fn_formatdatetime (@datetime smalldatetime, @formatstr varchar(255))
RETURNS varchar(255)
AS
BEGIN
-- This Function works similar to the Format function in Visual Basic for creating Custom Formating Strings for Date/Time Variables
-- Valid (case-sensitive) characters for the @formatstr Are...
-- d Displays the day as a number without a leading zero (1 - 31)
-- dd Displays the day as a number with a leading zero (01 - 31)
-- ddd Displays the day as an abbreviation (Sun - Sat)
-- dddd Displays the day as a full name (Sunday - Saturday)
-- w Displays the day of teh week as a number (1 for Sunday through 7 for Saturday)
-- m Displays the month as a number without a leading zero (1 - 12)
-- mm Displays the month as a number with a leading zero (01 - 12)
-- mmm Displays the month as an abbreviation (Jan - Dec)
-- mmmm Displays the month as a full month name (January - December)
-- yy Displays the year as a 2-digit number (00-99)
-- yyyy Displays the year as a 4-digit number (1000 - 9999)
-- q Displays the quarter of the year (1 - 4)
-- h Displays the hour as a number without leading zeros (0 - 23)
-- hh Displays the hour as a number with leading zeros (00 - 23)
-- th Displays the hour as a number without leading zeros (1 - 12)
-- n Displays the minute as a number without leading zeros (0 - 59)
-- nn Displays the minute as a number with leading zeros (00-59)
-- s Displays the second as a number without leading zeros (0 - 60)
-- ss Displays the second as a number with leading zeros (00 - 60)
-- am/pm Displays am before noon; Displays pm after noon through 11:59 P.M.
-- a/p Displays a before noon; Displays p after noon through 11:59 P.M.
-- Examples (assuming a date of March 7th, 2003 at 8:07:05 A.M.)
-- @formatstr Returns
-- m/d/yy 3/7/03
-- mmmm d, yyyy March 7, 2003
-- mm-dd-yyyy h:nnam/pm 03-07-2003 8:07am

DECLARE @outStr varchar(255)
DECLARE @datestr varchar(24)
DECLARE @meridian varchar(1)

SET @outStr = @formatstr
SET @datestr = CONVERT(varchar(24), @datetime, 113)

-- dddd --
SET @outStr = REPLACE(@outStr, 'dddd',
CASE DATEPART(dw, @datetime)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END)

-- ddd --
SET @outStr = REPLACE(@outStr, 'ddd',
CASE DATEPART(dw, @datetime)
WHEN 1 THEN 'Sun'
WHEN 2 THEN 'Mon'
WHEN 3 THEN 'Tue'
WHEN 4 THEN 'Wed'
WHEN 5 THEN 'Thu'
WHEN 6 THEN 'Fri'
WHEN 7 THEN 'Sat'
END)

-- dd --
SET @outStr = REPLACE(@outStr, 'dd', SUBSTRING(@datestr,1,2))

-- d --
SET @outStr = REPLACE(@outStr, 'd', CONVERT(int,SUBSTRING(@datestr,1,2)))

-- w --
SET @outStr = REPLACE(@outStr, 'w', DATEPART(dw,@datetime))

-- yyyy --
SET @outStr = REPLACE(@outStr, 'yyyy', SUBSTRING(@datestr,8,4))

-- yy --
SET @outStr = REPLACE(@outStr, 'yy', SUBSTRING(@datestr,10,2))

-- q --
SET @outStr = REPLACE(@outStr, 'q', DATEPART(q,@datestr))

-- hh --
SET @outStr = REPLACE(@outStr, 'hh', SUBSTRING(@datestr,13,2))

-- th --
IF CONVERT(int,SUBSTRING(@datestr,13,2)) > 12
SET @outStr = REPLACE(@outStr, 'th', CONVERT(int,SUBSTRING(@datestr,13,2)) - 12)
ELSE SET @outStr = REPLACE(@outStr, 'th', SUBSTRING(@datestr,13,2))

-- h --
SET @outStr = REPLACE(@outStr, 'h', CONVERT(int,SUBSTRING(@datestr,13,2)))

-- nn --
SET @outStr = REPLACE(@outStr, 'nn', SUBSTRING(@datestr,16,2))

-- n --
SET @outStr = REPLACE(@outStr, 'n', CONVERT(int,SUBSTRING(@datestr,16,2)))

-- ss --
SET @outStr = REPLACE(@outStr, 'ss', SUBSTRING(@datestr,19,2))

-- s --
SET @outStr = REPLACE(@outStr, 's', CONVERT(int,SUBSTRING(@datestr,19,2)))

-- m, mm, mmm, mmmm (This is last because it put letters back into the @outStr and if done previously, things like the 'h' in 'March' become an hour --
IF dbo.INSTR(1,@outStr,'m') > 0 BEGIN
IF dbo.INSTR(1,@outStr,'mm') > 0 BEGIN
IF dbo.INSTR(1,@outStr,'mmm') > 0 BEGIN
IF dbo.INSTR(1,@outStr,'mmmm') > 0 BEGIN
SET @outStr = REPLACE(@outStr, 'mmmm',
CASE DATEPART(mm, @datetime)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
END)
END
ELSE SET @outStr = REPLACE(@outStr, 'mmm', SUBSTRING(@datestr,4,3))
END
ELSE SET @outStr = REPLACE(@outStr, 'mm', DATEPART(mm,@datetime))
END
ELSE SET @outStr = REPLACE(@outStr, 'm', DATEPART(m,@datetime))
END

-- Used by am/pm and a/p --
IF CONVERT(int,SUBSTRING(@datestr,13,2)) > 12
SET @meridian = 'p'
ELSE SET @meridian = 'a'
-- am/pm --
SET @outStr = REPLACE(@outStr, 'am/pm', @meridian+'m')
-- a/p --
SET @outStr = REPLACE(@outStr, 'a/p', @meridian)

RETURN @outStr
END

 

[clarkin]

I wouldn't bother making my own VB-like INSTR() function. T-SQL already has CHARINDEX() and PATINDEX():
CHARINDEX ( expression1 , expression2 [ , start_location ] )

so instead of your INSTR(0, 'search this string for x', 'x'), you'd CHARINDEX('x', 'search this string for x', 0). I'd guess CHARINDEX is easier & faster

I haven't bothered looking at your format function, but i've found CONVERT(varchar, @someDatetimeVal, nnn) has plenty of options (specified by a number nnn). See your BOL entry:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp

or, just give me a datetime and an output your func creates and i'll try it with built in T-SQL

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[SlashZero]

Thanks! I looked for something like INSTR in the String Functions in my SQL Help but couldn't find anything like it. That helps a lot!

The CONVERT functin does provide several different formats but none of them quite matched the format I needed which is the reason for the format function. (&quot;d/m/yy h:mmam/pm&quot; as &quot;3/7/03 8:00pm&quot

If anybody does see anything that can be coded better or more effeciently, please let me know. (I've got my MCSE for Installing,Administering,Configurint SQL Server, but I'm still really new to Designing Transact-SQL code. Most of my experience comes from VB, VBScript, VBA, and SQL syntax used in Access)

 

[SQLBill]

SlashZero,

Once you get the code refined to where you want it to be, how about posting it in the FAQ list?

-SQLBill

 

[SlashZero]

Ok... using what clarkin explained... the nested IF block at the bottom can be changed to...

	IF CHARINDEX('m',@outStr,0) > 0 BEGIN
		IF CHARINDEX('mm',@outStr,0) > 0 BEGIN
			IF CHARINDEX('mmm',@outStr,0) > 0 BEGIN
				IF CHARINDEX('mmmm',@outStr,0) > 0 BEGIN
					SET @outStr = REPLACE(@outStr, 'mmmm',
						CASE DATEPART(mm, @datetime)
							WHEN 1 THEN 'January'
							WHEN 2 THEN 'February'
							WHEN 3 THEN 'March'
							WHEN 4 THEN 'April'
							WHEN 5 THEN 'May'
							WHEN 6 THEN 'June'
							WHEN 7 THEN 'July'
							WHEN 8 THEN 'August'
							WHEN 9 THEN 'September'
							WHEN 10 THEN 'October'
							WHEN 11 THEN 'November'
							WHEN 12 THEN 'December'
						END)
				END
				ELSE SET @outStr = REPLACE(@outStr, 'mmm', SUBSTRING(@datestr,4,3))
			END
			ELSE BEGIN
				SET @temp = DATEPART(mm,@datetime)
				IF (DATEPART(mm,@datetime)<10) SET @temp = '0' + @temp
				SET @outStr = REPLACE(@outStr, 'mm', @temp)
			END

		END
		ELSE SET @outStr = REPLACE(@outStr, 'm', DATEPART(mm,@datetime))
	END

And then also add

DECLARE @temp	varchar(2)

to the Declarations at the top.

This also fixes a bug I noticed with the 'mm' code not forcing a leading zero.

 

[SlashZero]

Ok... Another bug correction...
'm/d/yy h:nnam/pm' would replace the 'm's in 'am/pm' with the month, then the 'am/pm' conversion wouldn't work... So, if you would like to try the function, here is the whole thing... Let me know if anybody has any problems. (I dropped the comment section at the top to shorten the code)

CREATE FUNCTION fn_formatdatetime (@datetime smalldatetime, @formatstr varchar(255))  
RETURNS varchar(255)
AS
BEGIN 
	DECLARE @outStr	varchar(255)
	DECLARE @datestr	varchar(24)
	DECLARE @meridian	varchar(1)
	DECLARE @temp	varchar(2)
	
	SET @outStr = @formatstr
	SET @datestr = CONVERT(varchar(24), @datetime, 113)

	-- dddd --
	SET @outStr = REPLACE(@outStr, 'dddd',
		CASE DATEPART(dw, @datetime)
			WHEN 1 THEN 'Sunday'
			WHEN 2 THEN 'Monday'
			WHEN 3 THEN 'Tuesday'
			WHEN 4 THEN 'Wednesday'
			WHEN 5 THEN 'Thursday'
			WHEN 6 THEN 'Friday'
			WHEN 7 THEN 'Saturday'
		END)

	-- ddd --
	SET @outStr = REPLACE(@outStr, 'ddd',
		CASE DATEPART(dw, @datetime)
			WHEN 1 THEN 'Sun'
			WHEN 2 THEN 'Mon'
			WHEN 3 THEN 'Tue'
			WHEN 4 THEN 'Wed'
			WHEN 5 THEN 'Thu'
			WHEN 6 THEN 'Fri'
			WHEN 7 THEN 'Sat'
		END)

	-- dd --
	SET @outStr = REPLACE(@outStr, 'dd', SUBSTRING(@datestr,1,2))

	-- d --
	SET @outStr = REPLACE(@outStr, 'd', CONVERT(int,SUBSTRING(@datestr,1,2)))

	-- w --
	SET @outStr = REPLACE(@outStr, 'w', DATEPART(dw,@datetime))

	-- yyyy --
	SET @outStr = REPLACE(@outStr, 'yyyy', SUBSTRING(@datestr,8,4))

	-- yy --
	SET @outStr = REPLACE(@outStr, 'yy', SUBSTRING(@datestr,10,2))

	-- q --
	SET @outStr = REPLACE(@outStr, 'q', DATEPART(q,@datestr))

	-- hh --
	SET @outStr = REPLACE(@outStr, 'hh', SUBSTRING(@datestr,13,2))

	-- th --
	IF CONVERT(int,SUBSTRING(@datestr,13,2)) > 12
	SET @outStr = REPLACE(@outStr, 'th', CONVERT(int,SUBSTRING(@datestr,13,2)) - 12)
	ELSE SET @outStr = REPLACE(@outStr, 'th', SUBSTRING(@datestr,13,2))

	-- h --
	SET @outStr = REPLACE(@outStr, 'h', CONVERT(int,SUBSTRING(@datestr,13,2)))

	-- nn --
	SET @outStr = REPLACE(@outStr, 'nn', SUBSTRING(@datestr,16,2))

	-- n --
	SET @outStr = REPLACE(@outStr, 'n', CONVERT(int,SUBSTRING(@datestr,16,2)))

	-- ss --
	SET @outStr = REPLACE(@outStr, 'ss', SUBSTRING(@datestr,19,2))

	-- s --
	SET @outStr = REPLACE(@outStr, 's', CONVERT(int,SUBSTRING(@datestr,19,2)))

	-- m, mm, mmm, mmmm (This is last because it put letters back into the @outStr and if done previously, things like the 'h' in 'March' become an hour --
	IF CHARINDEX('m',@outStr,0) > 0 BEGIN
		IF CHARINDEX('mm',@outStr,0) > 0 BEGIN
			IF CHARINDEX('mmm',@outStr,0) > 0 BEGIN
				IF CHARINDEX('mmmm',@outStr,0) > 0 BEGIN
					SET @outStr = REPLACE(@outStr, 'mmmm',
						CASE DATEPART(mm, @datetime)
							WHEN 1 THEN 'January'
							WHEN 2 THEN 'February'
							WHEN 3 THEN 'March'
							WHEN 4 THEN 'April'
							WHEN 5 THEN 'May'
							WHEN 6 THEN 'June'
							WHEN 7 THEN 'July'
							WHEN 8 THEN 'August'
							WHEN 9 THEN 'September'
							WHEN 10 THEN 'October'
							WHEN 11 THEN 'November'
							WHEN 12 THEN 'December'
						END)
				END
				ELSE SET @outStr = REPLACE(@outStr, 'mmm', SUBSTRING(@datestr,4,3))
			END
			ELSE BEGIN
				SET @temp = DATEPART(mm,@datetime)
				IF (DATEPART(mm,@datetime)<10) SET @temp = '0' + @temp
				SET @outStr = REPLACE(@outStr, 'mm', @temp)
			END

		END
		ELSE BEGIN
			SET @outStr = REPLACE(@outStr, 'm', DATEPART(mm,@datetime))
			SET @outStr = REPLACE(@outStr, 'a'+CAST(DATEPART(mm,@datetime) AS varchar(1))+'/p'+CAST(DATEPART(mm,@datetime) AS varchar(1)),'am/pm')
		END
	END

	-- Used by AM/PM, am/pm, A/P, a/p --
	IF CONVERT(int,SUBSTRING(@datestr,13,2)) > 12
	SET @meridian = 'p'
	ELSE SET @meridian = 'a'
	-- am/pm --
	SET @outStr = REPLACE(@outStr, 'am/pm', @meridian+'m')
	-- a/p --
	SET @outStr = REPLACE(@outStr, 'a/p', @meridian)
	
	RETURN @outStr
END

SQLBill,
I will post it to the FAQs once I'm convinced everything is working as intended.