More SQL Server Date Processing

In my previous post I mentioned a few useful tidbits when working with dates, today I’ve been working on a comprehensive date formatting function for SQL Server which can accept an explicit .NET DateTime.ToString() style Custom Date Format string.

SQL FormatDate Function

IF  EXISTS (
    SELECT * 
    FROM sys.objects 
    WHERE
        object_id = OBJECT_ID(N'[dbo].[fnFormatDate]') AND 
        type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnFormatDate]
GO
CREATE FUNCTION dbo.fnFormatDate 
(
    @DateValue datetime,
    @DateFormat nvarchar(50)
)
RETURNS nvarchar(50)
AS
BEGIN
    -- Temporarily replace formats with reserved characters 
    -- (so that the 'd' in 'Tuesday' etc. doesn't get replaced)
    SET @DateFormat = REPLACE(@DateFormat, 'dddd', '*AA*')
    SET @DateFormat = REPLACE(@DateFormat, 'ddd', '*A*')
    SET @DateFormat = REPLACE(@DateFormat, 'mmmm', '*BB*')
    SET @DateFormat = REPLACE(@DateFormat, 'mmm', '*B*')

    SET @DateFormat = REPLACE(@DateFormat, 'dd', 
        RIGHT('00' + DATENAME(day, @DateValue), 2))
    SET @DateFormat = REPLACE(@DateFormat, 'd', 
        DATENAME(day, @DateValue))

    SET @DateFormat = REPLACE(@DateFormat, 'mm', 
        RIGHT('00' + CAST(DATEPART(month, @DateValue) as varchar(2)), 2))
    SET @DateFormat = REPLACE(@DateFormat, 'm', 
        CAST(DATEPART(month, @DateValue) as varchar(2)))

    SET @DateFormat = REPLACE(@DateFormat, 'yyyy', 
        DATENAME(year, @DateValue))
    SET @DateFormat = REPLACE(@DateFormat, 'yy', 
        RIGHT('00' + CAST(DATEPART(year, @DateValue) as varchar(4)), 2))


    SET @DateFormat = REPLACE(@DateFormat, '*AA*', 
        DATENAME(weekday, @DateValue))
    -- NOTE: The 'ddd' format is not culture agnostic, for some languages this will be incorrect
    SET @DateFormat = REPLACE(@DateFormat, '*A*', 
        LEFT(DATENAME(weekday, @DateValue), 3))

    SET @DateFormat = REPLACE(@DateFormat, '*BB*', 
        DATENAME(month, @DateValue))
    -- NOTE: The 'mmm' format is not culture agnostic, for some languages this will be incorrect
    SET @DateFormat = REPLACE(@DateFormat, '*B*', 
        LEFT(DATENAME(month, @DateValue), 3))

    RETURN @DateFormat 
END

Example Usage

DECLARE @TestDate datetime
SET @TestDate = '2001-02-03T04:05:06'
SELECT 
    Format, 
    dbo.fnFormatDate(@TestDate, Format)
FROM
    (SELECT 'dd/mm/yyyy' Format
    UNION ALL
    SELECT 'mm/dd/yyyy'
    UNION ALL
    SELECT 'dd mmmm yyyy'
    UNION ALL
    SELECT 'dd mmm yyyy'
    UNION ALL
    SELECT 'ddd dd mmm yyyy'
    UNION ALL
    SELECT 'dddd dd mmm yyyy'
    UNION ALL
    SELECT 'dddd d mmm yy'
    UNION ALL
    SELECT 'yyyy-mm-dd'
    UNION ALL
    SELECT 'yy-mm-dd') Formats

Warning: I don’t make any claims about the performance of this function, so use it judiciously in your queries. If performance is a major concern then using a SQL-CLR assembly might be the better option (more accurate results also).

Feel free to post a comment if you find this useful

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: