Technical Article

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