SQL Server: Formatting DateTime values

Here’s a little quickie that should help you easily format date values in SQL Server queries (with or without a time portion). I have been using this UDF for many years, after I found it somewhere on the web. That is, I can’t take any credit for the original portion (which already contained a couple of target-formats), but back when I found it, I didn’t make a note of the original author. Over the years I added new formats whenever I needed them, so there’s quite a few at present.

Here’s the ready-to-paste UDF:

/*

	Returns the formatted equivalent of the passed date/datetime.

*/
CREATE FUNCTION [dbo].[f_FormatDateTime] 
( 
    @dte DATETIME, 
    @format VARCHAR(16) 
) 
RETURNS VARCHAR(64) 
AS 
BEGIN 
	DECLARE @dtVC VARCHAR(64) 
	SELECT @dtVC = CASE @format 

	/*
		All samples for the formats below have been created with ...
	
		DECLARE @dte datetime;
		SET @dte = '20091228 18:45:00.000';
		SELECT [dbo].[f_FormatDateTime](@dte,'[the format]');	
	
		... and thus relate to Dec. 28, 2009 (6:45 pm).
	*/

	-- Returns i.e. "Monday, December 28, 2009"
	WHEN 'LONGDATE' THEN 
		DATENAME(dw, @dte) 
		+ ',' + SPACE(1) + DATENAME(m, @dte) 
		+ SPACE(1) + CAST(DAY(@dte) AS VARCHAR(2)) 
		+ ',' + SPACE(1) + CAST(YEAR(@dte) AS CHAR(4)) 

	-- Returns i.e. "Monday, December 28, 2009  6:45:00 PM"
	WHEN 'LONGDATEANDTIME' THEN 
		DATENAME(dw, @dte) 
		+ ',' + SPACE(1) + DATENAME(m, @dte) 
		+ SPACE(1) + CAST(DAY(@dte) AS VARCHAR(2)) 
		+ ',' + SPACE(1) + CAST(YEAR(@dte) AS CHAR(4)) 
		+ SPACE(1) + RIGHT(CONVERT(CHAR(20), 
		@dte - CONVERT(DATETIME, CONVERT(CHAR(8), 
		@dte, 112)), 22), 11) 

	-- Returns i.e. "Dec 28 2009"
	WHEN 'SHORTDATE' THEN 
		LEFT(CONVERT(CHAR(19), @dte, 0), 11) 

	-- Returns i.e. "Dec 28 2009  6:45 PM" 
	WHEN 'SHORTDATEANDTIME' THEN 
		REPLACE(REPLACE(CONVERT(CHAR(19), @dte, 0), 'AM', ' AM'), 'PM', ' PM') 

	-- Returns i.e. "1262025985" 
	WHEN 'UNIXTIMESTAMP' THEN 
		CAST(DATEDIFF(SECOND, '19700101', @dte) AS VARCHAR(64)) 

	-- Returns i.e. "20091228" 
	WHEN 'YYYYMMDD' THEN 
		CONVERT(CHAR(8), @dte, 112) 

	-- Returns i.e. "2009-12-28" 
	WHEN 'YYYY-MM-DD' THEN 
		CONVERT(CHAR(10), @dte, 23) 

	-- Returns i.e. "20091228" 
	WHEN 'YYMMDD' THEN 
		CONVERT(VARCHAR(8), @dte, 12) 

	-- Returns i.e. "09-12-28" 
	WHEN 'YY-MM-DD' THEN 
		STUFF(STUFF(CONVERT(VARCHAR(8), @dte, 12), 5, 0, '-'), 3, 0, '-') 

	-- Returns i.e. "091228"
	WHEN 'MMDDYY' THEN 
		REPLACE(CONVERT(CHAR(8), @dte, 10), '-', SPACE(0)) 

	-- Returns i.e. "12-28-09" 
	WHEN 'MM-DD-YY' THEN 
		CONVERT(CHAR(8), @dte, 10) 

	-- Returns i.e. "12/28/09" 
	WHEN 'MM/DD/YY' THEN 
		CONVERT(CHAR(8), @dte, 1) 

	-- Returns i.e. "12/28/2009" 
	WHEN 'MM/DD/YYYY' THEN 
		CONVERT(CHAR(10), @dte, 101) 

	-- Returns i.e. "281209" 
	WHEN 'DDMMYY' THEN 
		REPLACE(CONVERT(CHAR(8), @dte, 3), '/', SPACE(0)) 

	-- Returns i.e. "28-12-09" 
	WHEN 'DD-MM-YY' THEN 
		REPLACE(CONVERT(CHAR(8), @dte, 3), '/', '-') 

	-- Returns i.e. "12/28/09" 
	WHEN 'DD/MM/YY' THEN 
		CONVERT(CHAR(8), @dte, 3) 

	-- Returns i.e. "28.12.09" 
	WHEN 'DD.MM.YY' THEN
		CONVERT(CHAR(8), @dte, 4) 

	-- Returns i.e. "28/12/2009" 
	WHEN 'DD/MM/YYYY' THEN 
		CONVERT(CHAR(10), @dte, 103) 

	-- Returns i.e. "28.12.2009" 
	WHEN 'DD.MM.YYYY' THEN
		CONVERT(CHAR(10), @dte, 104) 

	-- Returns i.e. "18:45:00" 
	WHEN 'HH:MM:SS 24' THEN 
		CONVERT(CHAR(8), @dte, 8) 

	-- Returns i.e. "18:45" 
	WHEN 'HH:MM 24' THEN 
		LEFT(CONVERT(VARCHAR(8), @dte, 8), 5) 

	-- Returns i.e. "6:45:00 PM" 
	WHEN 'HH:MM:SS 12' THEN 
		LTRIM(RIGHT(CONVERT(VARCHAR(20), @dte, 22), 11)) 

	-- Returns i.e. "6:45 PM" 
	WHEN 'HH:MM 12' THEN 
		LTRIM(SUBSTRING(CONVERT(VARCHAR(20), @dte, 22), 10, 5) 
			+ RIGHT(CONVERT(VARCHAR(20), @dte, 22), 3)) 

	ELSE 
		'Invalid format specified' 

	END 
	RETURN @dtVC 
END

Call it i.e. with a simple …

SELECT dbo.f_FormatDateTime(GETDATE() ,'MM/DD/YYYY');

Happy coding!