Convert T-SQL DateTime to strings using format masks like .Net supports

This T-SQL function is based off something I ran across somewhere on the web but don't recall where. I made it to support almost all the formatting masks and such that .Net supports with it's string formatting for dates and times. It's handy if Sql server doesn't  have a Convert() that fits your needs.

Normally formatting dates in the database isn't recommended but I've had cause to write procs that return XML and needed dates in specific formats. Works in SQL 2005 or SQL 2008, if you don't need to support 2005 there's some code commented out inside for time zones that's more efficient.

The code looks all mangled in this blog post, but cut and paste it into SQL Management Studio and it looks good. There's some sample calls near the bottom to give you an idea of the format mask styles supported.

-- ------------------------------------------------------------------------------------

-- This function supports date and time formatting using roughly the format masks supported

-- in .Net.

-- 

-- It supports most of the date/time formatting syntax. Notable exceptions are fractional seconds

-- and some of the timezone variants. It also doesn't support true abbreviated months it

-- just takes the leftmost 3 characters which works for English.

-- ------------------------------------------------------------------------------------

CREATE FUNCTION dbo.fnc_FormatDate (@DateTimeToFormat DATETIME, @FormatMask VARCHAR(50))

RETURNS nvarchar(100)

AS

BEGIN

    DECLARE @FormattedResult nvarchar(50)

    SET @FormattedResult = @FormatMask

    IF (charindex('YYYY', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'YYYY', datename(YY, @DateTimeToFormat))

    IF (charindex('YY', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'YY', right(datename(YY, @DateTimeToFormat), 2))

    IF (charindex('MMMM', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'MMMM', datename(MM, @DateTimeToFormat))

    IF (charindex('MMM', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'MMM', left(datename(MM, @DateTimeToFormat), 3))

    IF (charindex('MM', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'MM', right('0' + cast(datepart(MM, @DateTimeToFormat) as nvarchar(2)), 2))

    IF (charindex('M', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'M', cast(datepart(MM, @DateTimeToFormat) as nvarchar(2)))

    IF (charindex('DD', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'DD', right('0' + datename(DD, @DateTimeToFormat), 2))

    IF (charindex('D', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'D', datename(DD, @DateTimeToFormat))  

    IF (charindex('HH', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'HH', right('0' + datename(HH, @DateTimeToFormat), 2))

    IF (charindex('H', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'H', datename(HH, @DateTimeToFormat))

    IF (charindex('hh', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'hh',right('0' + cast((datepart(HH, @DateTimeToFormat) + 11) % 12 + 1 as nvarchar(2)), 2))

    IF (charindex('h', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'h', cast((datepart(HH, @DateTimeToFormat) + 11) % 12 + 1 as nvarchar(2)))

    IF (charindex('mm', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'mm', right('0' + datename(Minute, @DateTimeToFormat), 2))

    IF (charindex('m', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'm', datename(Minute, @DateTimeToFormat))

    IF (charindex('ss', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'ss', right('0' + datename(Second, @DateTimeToFormat), 2))

    IF (charindex('s', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 's', datename(Second, @DateTimeToFormat))

    IF (charindex('tt', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'tt', CASE WHEN datename(Hour, @DateTimeToFormat) < 12 THEN 'AM' ELSE 'PM' END)

    IF (charindex('t', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 't', CASE WHEN datename(Hour, @DateTimeToFormat) < 12 THEN 'A' ELSE 'P' END)

    IF (charindex('zzzz', @FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)

    BEGIN

       -- If SQL 2008+ the line below can replace the code within this block

       -- SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'zzzz', right(cast(SYSDATETIMEOFFSET() as nvarchar(50)), 6))

      

       DECLARE @GmtOffset int

       DECLARE @GmtSign nvarchar(1)

       SET @GmtOffset = datediff(minute, getutcdate(), getdate())

       SELECT @GmtSign = CASE WHEN @GmtOffset >= 0 THEN '+' ELSE '-' END

   

       SET @FormattedResult = replace(@FormattedResult COLLATE SQL_Latin1_General_CP1_CS_AS, 'zzzz', CASE WHEN @GmtOffset >= 0 THEN '+' ELSE '-' END + right('0' + cast(abs(@GmtOffset) / 60 as nvarchar(5)),2) + ':' + right('0' + cast(abs(@GmtOffset % 60) as nvarchar(2)), 2))

    END

   

RETURN @FormattedResult

-- SELECT dbo.fnc_FormatDate(getdate(), 'MM/DD/YYYY HH:mm:ss tt')

-- SELECT dbo.fnc_FormatDate(getdate(), 'MMM DD, YYYY hh:mm:ss zzzz')

-- SELECT dbo.fnc_FormatDate(getdate(), 'MMMM DD, YY HH:mm:ss tt')

-- SELECT dbo.fnc_FormatDate(getdate(), 'M/D/YY H:m:s t')

END