Format dates and times in T-SQL

I stole this script off of the internet a few years ago. I wish I could give the author credit for it, but I can't remember who he is. If anyone knows, let me know and I'll add him to this post.

This function allows you to do date/time formatting just like you do it in C# or VB.NET.

I find it enormously useful. My code is littered with calls to it.


USE [ncu]
GO
/****** Object:  UserDefinedFunction [dbo].[FormatDateTime]    Script Date: 01/27/2011 17:31:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FormatDateTime]
                      (@f_datetime DATETIME
                     , @f_format   VARCHAR (60))
RETURNS VARCHAR (60)
AS
/*
||======================================================================
|| Purpose:  Provide a more intuitive method of converting DATETIME
||           values to character.  Only the parts specified within
||           the input format request are sent back, and any other
||           characters are returned in their respective places.
||
||           Here are the special-request symbols:
||
||           [YY] or [YYYY]   -> year
||           [MM] or [M]      -> month (numeric; 'M' means one digit
||                                      where possible)
||           [DD] or [D]      -> day
||           [DWEEK]          -> day of week
||           [DWK]            -> 3-characters day of week abbreviation
||           [MON]            -> 3-character month abbreviation
||           [MONTH]          -> entire month name
||           [HH] or [H]      -> hour
||           [24HH] or [24H]  -> 24-hour clock
||           [mm] or [m]      -> minute
||           [NN] or [N]      -> minute
||           [SS] or [S]      -> second
||           [AA] or [PP]     -> 'AM' or 'PM'
||           [AM] or [PM]     -> 'AM' or 'PM'
||
||           Every other character is considered part of the output.
||======================================================================
*/
BEGIN
  /*
  ||====================================================================
  || Data declarations...
  ||====================================================================
  */
  DECLARE @datetime    VARCHAR (60)
        , @symbol      VARCHAR (7)
        , @translation VARCHAR (9)
        , @hhmmss      CHAR (8)
        , @year        INT
        , @month       INT
        , @day         INT
        , @hour        INT
        , @minute      INT
        , @second      INT
        , @pos         INT
        , @pos2        INT
        , @len         INT

  /*
  ||====================================================================
  || Validations...
  ||====================================================================
  */
  /*
  ||====================================================================
  || A NULL input earns a NULL output...
  ||====================================================================
  */
  IF @f_datetime IS NULL
  OR @f_format   IS NULL
--THEN
    GOTO FNC_EXIT
--END IF

  /*
  ||====================================================================
  || End of validations...
  ||====================================================================
  */
  /*
  ||====================================================================
  || Main logic...
  ||====================================================================
  */
  /*
  ||====================================================================
  || Acquire the value of each of the date-time components in integer
  || form...
  ||====================================================================
  */
  SELECT @hhmmss   = CONVERT (VARCHAR, @f_datetime, 8)
       , @datetime = @f_format
  SELECT @year     = YEAR  (@f_datetime)
       , @month    = MONTH (@f_datetime)
       , @day      = DAY   (@f_datetime)
       , @hour     = CONVERT (INT, LEFT (@hhmmss, 2))
       , @minute   = CONVERT (INT, SUBSTRING (@hhmmss, 4, 2))
       , @second   = CONVERT (INT, RIGHT (@hhmmss, 2))

  /*
  ||====================================================================
  || For users' convenience, we allow small-case m to denote minutes
  || large-case M to denote month; or they can use the unintuitive
  || 'N' to denote minutes.
  ||
  || However, in order to support case-seensitivity even in a case-
  || insensitive environment, the code below locates each special symbol
  || within the specified format and replaces small-case '[m]' and
  || '[mm]' with '[N]' and '[NN]', or else all other small-case letters
  || with their large-case equivalents...
  ||====================================================================
  */
  SELECT @pos = CHARINDEX ('[', @datetime)

  WHILE @pos > 0
  BEGIN
    SELECT @pos2 = CHARINDEX (']', @datetime, @pos)

    IF @pos2 = 0
  --THEN
      SELECT @pos = 0
    ELSE
      BEGIN
        SELECT @len    = @pos2 - @pos + 1
             , @symbol = SUBSTRING (@datetime, @pos, @len)

        IF CONVERT (VARBINARY (120), @symbol)
         = CONVERT (VARBINARY (120), '[m]')
      --THEN
          SELECT @symbol = '[N]'

        ELSE IF CONVERT (VARBINARY (120), @symbol)
              = CONVERT (VARBINARY (120), '[mm]')
      --THEN
          SELECT @symbol = '[NN]'
        ELSE
          SELECT @symbol = UPPER (@symbol)
      --END IF

        SELECT @translation =
          CASE @symbol
            WHEN '[YYYY]' -- year
            THEN CONVERT (CHAR (4), @year)
            WHEN '[YY]'   -- year
            THEN RIGHT (CONVERT (CHAR (4), @year), 2)
            WHEN '[MONTH]'
            THEN UPPER (DATENAME (MM, @f_datetime))
            WHEN '[MON]'
            THEN LEFT (UPPER (DATENAME (MM, @f_datetime)), 3)
            WHEN '[M]'
            THEN CONVERT (VARCHAR, @month)
            WHEN '[MM]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @month), 2)
            WHEN '[D]'
            THEN CONVERT (VARCHAR, @day)
            WHEN '[DD]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @day), 2)
            WHEN '[DWEEK]'
            THEN UPPER (DATENAME (DW, @f_datetime))
            WHEN '[DWK]'
            THEN UPPER (LEFT (DATENAME (DW, @f_datetime), 3))
            WHEN '[H]'
            THEN
              CASE
                WHEN @hour BETWEEN 1 AND 12
                THEN CONVERT (VARCHAR, @hour)
                ELSE CONVERT (VARCHAR, ABS (@hour - 12))
              END
            WHEN '[HH]'
            THEN
              CASE
                WHEN @hour BETWEEN 1 AND 12
                THEN RIGHT ('0' + CONVERT (VARCHAR, @hour), 2)
                ELSE RIGHT ('0' + CONVERT (VARCHAR, ABS (@hour - 12)), 2)
              END
            WHEN '[24H]'
            THEN CONVERT (VARCHAR, @hour)
            WHEN '[24HH]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @hour), 2)
            WHEN '[N]'
            THEN CONVERT (VARCHAR, @minute)
            WHEN '[NN]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @minute), 2)
            WHEN '[S]'
            THEN CONVERT (VARCHAR, @second)
            WHEN '[SS]'
            THEN RIGHT ('0' + CONVERT (VARCHAR, @second), 2)  
            ELSE
              CASE
                WHEN @symbol IN ('[AM]', '[PM]', '[AA]', '[PP]')
                THEN
                  CASE
                    WHEN @hour < 12
                    THEN 'AM'
                    ELSE 'PM'
                  END
                ELSE @symbol
              END
          END

        SELECT @datetime = STUFF (@datetime, @pos, @len, @translation)
             , @pos      = CHARINDEX ('[', @datetime, @pos + LEN (@translation))
      END
  --END IF
  END

FNC_EXIT:
  RETURN @datetime
END