Formatting Dates

,

There are many ways to format dates, and rather that reinvent the wheel each time I've found it helpful to have a user defined function always available. As a function it of courses processes on each row, and to enable the most flexibility the formatting style is passed simply as a parameter. Year month and date could be handled more easily simply calling Year(), Month() and Date(), however including those as format options allowed the intiating process (procedure, report, whatever) to skip that logic and let the one function do everything.

Like any submitted script, there is always room for improvement. And this script could can surely be improved upon. If anyone has any comments or suggestions I would really appreciate hearing it.

cheers!
ry..

use [model]

BEGIN TRANSACTION


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

GO
CREATE FUNCTION dbo.udf_FormatDate (@datInputDate smalldateTime, @intFormat int = 0)
/*	Royal Roads University
	Jan 10th, 2001
	Ryan Brochez
	purpose:	custom format a date field
	pre:		@datInputDate - the date to format
			@intFormat - code to determine exactly how they want the name formatted
			7 - RRU standard 		- 1984/01/07
			6 - technical short form	- 01/07/1984
			5 - year only  			- 1984
			4 - month only			- Jan
			3 - day only			- 7
			2 - long date 			- January 7th, 1984
			1 - short date (default)	- Jan-07-1984
	post:		returns a string storing the date
*/
	RETURNS nvarchar(20)
AS
BEGIN

	DECLARE @strOutputDate nvarchar(20)


	-- check for a valid date	
	IF @datInputDate Is Null SET @strOutputDate = ''

	-- RRU Standard
	ELSE IF @intFormat = 7
		BEGIN

			-- start with the year
			SET @strOutputDate = (SELECT Convert(varchar(4), Year(@datInputDate)))
			SET @strOutputDate = @strOutputDate + '/'
		
			-- add a month and check for padding
			IF Month(@datInputDate) < 10 
				SET @strOutputDate = @strOutputDate + '0' + Convert(varchar(2), Month(@datInputDate))
			ELSE 
				SET @strOutputDate = @strOutputDate + Convert(varchar(2), Month(@datInputDate))
			SET @strOutputDate = @strOutputDate + '/'
			
			-- concatenate the day
			IF Day(@datInputDate) < 10 SET @strOutputDate = (@strOutputDate + '0' + Convert(varchar(2), Day(@datInputDate)))
			ELSE SET @strOutputDate = (@strOutputDate + Convert(varchar(2), Day(@datInputDate)))


		END


	-- technical short form
	ELSE IF @intFormat = 6
		BEGIN

			-- start with the month code
			IF Month(@datInputDate) < 10 
				SET @strOutputDate = '0' + Convert(varchar(2), Month(@datInputDate))
			ELSE 
				SET @strOutputDate = Convert(varchar(2), Month(@datInputDate))
			SET @strOutputDate = @strOutputDate + '/'
			
			-- concatenate the day
			IF Day(@datInputDate) < 10 SET @strOutputDate = (@strOutputDate + '0' + Convert(varchar(2), Day(@datInputDate)))
			ELSE SET @strOutputDate = (@strOutputDate + Convert(varchar(2), Day(@datInputDate)))
			SET @strOutputDate = @strOutputDate + '/'

			-- concatenate the year
			SET @strOutputDate = @strOutputDate + (SELECT Convert(varchar(4), Year(@datInputDate)))

		END

	-- year only
	ELSE IF @intFormat = 5
		BEGIN

			SET @strOutputDate = (SELECT Convert(varchar(4), Year(@datInputDate)))

		END

	-- month only
	ELSE IF @intFormat = 4
		BEGIN
			SET @strOutputDate = CASE Month(@datInputDate)
						WHEN 1 THEN 'Jan'
						WHEN 2 THEN 'Feb'
						WHEN 3 THEN 'Mar'
						WHEN 4 THEN 'Apr'
						WHEN 5 THEN 'May'
						WHEN 6 THEN 'Jun'
						WHEN 7 THEN 'Jul'
						WHEN 8 THEN 'Aug'
						WHEN 9 THEN 'Sep'
						WHEN 10 THEN 'Oct'
						WHEN 11 THEN 'Nov'
						ELSE 'Dec'
						END		
		END

	-- day only
	ELSE IF @intFormat = 3
		BEGIN			
			IF Day(@datInputDate) < 10 SET @strOutputDate = ('0' + CONVERT(varchar(2), Day(@datInputDate)))
			ELSE SET @strOutputDate = (Convert(varchar(2), Day(@datInputDate)))
		END

	-- long date
	ELSE IF @intFormat = 2
		BEGIN

			-- start with the month code
			SET @strOutputDate = CASE Month(@datInputDate)
						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'
						ELSE 'December'
						END		
			SET @strOutputDate = @strOutputDate + ' '
			
			-- concatenate the day
			SET @strOutputDate = (@strOutputDate + Convert(varchar(2), Day(@datInputDate)))
			SET @strOutputDate = @strOutputDate + CASE Day(@datInputDate)
								WHEN 1 THEN 'st, '
								WHEN 2 THEN 'nd, '
								WHEN 3 THEN 'rd, '
								ELSE 'th, '
								END

			-- concatenate the year
			SET @strOutputDate = @strOutputDate + (SELECT Convert(varchar(4), Year(@datInputDate)))

		END

	-- short date (default)
	ELSE 
		BEGIN
			-- start with the month code
			SET @strOutputDate = CASE Month(@datInputDate)
						WHEN 1 THEN 'Jan'
						WHEN 2 THEN 'Feb'
						WHEN 3 THEN 'Mar'
						WHEN 4 THEN 'Apr'
						WHEN 5 THEN 'May'
						WHEN 6 THEN 'Jun'
						WHEN 7 THEN 'Jul'
						WHEN 8 THEN 'Aug'
						WHEN 9 THEN 'Sep'
						WHEN 10 THEN 'Oct'
						WHEN 11 THEN 'Nov'
						ELSE 'Dec'
						END		
			SET @strOutputDate = @strOutputDate + '-'
			
			-- concatenate the day
			IF Day(@datInputDate) < 10 SET @strOutputDate = (@strOutputDate + '0' + Convert(varchar(2), Day(@datInputDate)))
			ELSE SET @strOutputDate = (@strOutputDate + Convert(varchar(2), Day(@datInputDate)))
			SET @strOutputDate = @strOutputDate + '-'

			-- concatenate the year
			SET @strOutputDate = @strOutputDate + (SELECT Convert(varchar(4), Year(@datInputDate)))

		END


RETURN(@strOutputDate)
END



GO

COMMIT TRANSACTION

/*  calling code

	SELECT 	dbo.udf_FormatDate('2005-9-16', 7) as [datStartDate]
	SELECT 	dbo.udf_FormatDate('2005-9-16', 6) as [datStartDate]
	SELECT 	dbo.udf_FormatDate('2005-9-16', 5) as [datStartDate]
	SELECT 	dbo.udf_FormatDate('2005-9-16', 4) as [datStartDate]
	SELECT 	dbo.udf_FormatDate('2005-9-16', 3) as [datStartDate]
	SELECT 	dbo.udf_FormatDate('2005-9-16', 2) as [datStartDate]
	SELECT 	dbo.udf_FormatDate('2005-9-16', 1) as [datStartDate]

*/

Rate

Share

Share

Rate