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]
*/
