Custom Date Format Functions in SQL Server

Note: Code is SQL Server 2000

This is a repost of my original short article on how to implement a custom date format for SQL Server, using a User-defined Function. As well, I hope this post helps to illustrate how easy it is to implement a User-defined Function in SQL Server (UDF).

This is often a great time saving when it comes to formulating SQL syntax, which can sometimes be very long-winded, especially if you want all the DATEPART's to appear in the same field because then you have do CAST'ing into a text-base datatype. Following is a script to create three example format Functions, which you can reuse in any query (example further down).

USE Northwind
CREATE FUNCTION just_date_simple(@dtvalue datetime) 
RETURNS nvarchar(10)
AS
BEGIN 
DECLARE @display nvarchar(10)
SET @display = CAST(DATEPART(mm, @dtvalue) AS nvarchar) + '/' + CAST(DATEPART(dd, @dtvalue) AS nvarchar) + '/' + CAST(DATEPART(yyyy, @dtvalue) AS nvarchar)
RETURN @display
END
GO
CREATE FUNCTION just_date_readable(@dtvalue datetime) 
RETURNS nvarchar(30)
AS
BEGIN 
DECLARE @display nvarchar(30)
SET @display = CAST(DATENAME(mm, @dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(dd, @dtvalue) AS nvarchar) + ', ' + CAST(DATEPART(yyyy, @dtvalue) AS nvarchar)
RETURN @display
END
GO
CREATE FUNCTION just_date_formal(@dtvalue datetime) 
RETURNS nvarchar(40)
AS
BEGIN 
DECLARE @display nvarchar(40)
SET @display = CAST(DATENAME(dw, @dtvalue) AS nvarchar) + ', ' + CAST(DATEPART(dd, @dtvalue) AS nvarchar) + ' ' + CAST(DATENAME(mm, @dtvalue) AS nvarchar) + ' ' + CAST(DATEPART(yyyy, @dtvalue) AS nvarchar)
RETURN @display
END
GO

Following is an example query against the Northwind sample database, which redisplays the same record three times but where each shows a differently formatted version of the same date.

USE Northwind
SELECT TOP 1 OrderID, CustomerID, dbo.just_date_simple(OrderDate) AS OrderDate FROM Orders
UNION ALL 
SELECT TOP 1 OrderID, CustomerID, dbo.just_date_readable(OrderDate) AS OrderDate FROM Orders
UNION ALL 
SELECT TOP 1 OrderID, CustomerID, dbo.just_date_formal(OrderDate) AS OrderDate FROM Orders

Following is the output, as shown in Query Analyzer.

This is a repost of the original article, and thank you for all the comments and compliments from my original posting.