formatDate_Recursive_iTVF.sql #blog

formatDate_Recursive_iTVF.sql

-- http://spaghettidba.com/2012/03/23/sql-server-and-custom-date-formats/
-- =============================================
-- Author:		Gianluca Sartori - @spaghettidba
-- Create date: 2011-10-14
-- Description:	Formats a date using the supplied 
--              format string
-- =============================================
CREATE FUNCTION [dbo].[formatDateWithReplace](@date AS datetime, @format_string varchar(50))
RETURNS TABLE
AS
RETURN (
    -- ================================================ 
    -- create the allowedTokens list
    -- ================================================ 
    WITH allowedTokens (id, code, value) AS (
        SELECT id, 
            code COLLATE Latin1_General_CS_AS, 
            value 
        FROM (
                      SELECT  1, 'YYYY', RIGHT('0000' + CAST(YEAR(@date) AS varchar(4)),4)
            UNION ALL SELECT  2, 'YY',   RIGHT('00' + CAST(YEAR(@date) AS varchar(4)),2) 
            UNION ALL SELECT  3, 'Y',    CAST(CAST(RIGHT('00' + CAST(YEAR(@date) AS varchar(4)),2) AS int) AS varchar(2))
            UNION ALL SELECT  4, 'MMMM', CHAR(1)
            UNION ALL SELECT  5, 'MM',   RIGHT('00' + CAST(MONTH(@date) AS varchar(2)),2)
            UNION ALL SELECT  6, 'M',    CAST(MONTH(@date) AS varchar(2))
            UNION ALL SELECT  7, 'DDDD', CHAR(2)
            UNION ALL SELECT  8, 'DD',   RIGHT('00' + CAST(DAY(@date) AS varchar(2)),2)
            UNION ALL SELECT  9, 'D',    CAST(DAY(@date) AS varchar(2))
            UNION ALL SELECT 10, 'HH',   RIGHT('00' + CAST(DATEPART(hour,@date) AS varchar(2)),2)
            UNION ALL SELECT 11, 'H',    CAST(DATEPART(hour,@date) AS varchar(2))
            UNION ALL SELECT 12, 'hh',   RIGHT('00' + CAST(DATEPART(hour, @date) - (12 * CEILING((DATEPART(hour, @date) - 12)*.1)) AS varchar(2)),2)
            UNION ALL SELECT 13, 'h',    CAST(DATEPART(hour, @date) - (12 * CEILING((DATEPART(hour, @date) - 12)*.1)) AS varchar(2))
            UNION ALL SELECT 14, 'mm',   RIGHT('00' + CAST(DATEPART(minute,@date) AS varchar(2)),2)
            UNION ALL SELECT 15, 'm',    CAST(DATEPART(minute,@date) AS varchar(2))
            UNION ALL SELECT 16, 'ss',   RIGHT('00' + CAST(DATEPART(second,@date) AS varchar(2)),2)
            UNION ALL SELECT 17, 's',    CAST(DATEPART(second,@date) AS varchar(2))
            UNION ALL SELECT 18, 'fff',  RIGHT('000' + CAST(DATEPART(millisecond,@date) AS varchar(3)),3)
            UNION ALL SELECT 19, 'f',    CAST(DATEPART(millisecond,@date) AS varchar(3))
            UNION ALL SELECT 20, 'tt',   CASE WHEN DATEPART(hour,@date) >= 12 THEN 'PM' ELSE 'AM' END
            UNION ALL SELECT 21, 't',    CASE WHEN DATEPART(hour,@date) >= 12 THEN 'P' ELSE 'A' END
            UNION ALL SELECT 22, CHAR(1),DATENAME(month, @date)
            UNION ALL SELECT 23, CHAR(2),DATENAME(weekday, @date)
        ) AS susbst (id, code, value)
    ),
    -- ================================================ 
    -- create the list of values to replace in the input
    -- ================================================ 
    substitutions (id, code, value, maxval) AS (
        SELECT ROW_NUMBER() OVER (ORDER BY id, set_id), code, value, COUNT(*) OVER ()
        FROM (
            SELECT 0 AS set_id, id, code, value
            FROM allowedTokens
        ) AS src
    ),
    -- ================================================ 
    -- make the format string case-sensitive
    -- ================================================ 
    formatStrings (formatString) AS (
        SELECT @format_string COLLATE Latin1_General_CS_AS
    ),
    -- ================================================ 
    -- build a recursive CTE to replace tokens one at a time
    -- ================================================ 
    recursiveReplace AS (
        SELECT s.id, 
            REPLACE(
                f.formatString, 
                s.code, 
                s.value) AS formattedDate,
                s.maxval
        FROM formatStrings AS f
        INNER JOIN substitutions AS s
            ON s.id = 1
        UNION ALL
        SELECT s.id, 
            REPLACE(
                r.formattedDate,
                s.code, 
                s.value) AS formattedDate,
                s.maxval
        FROM recursiveReplace AS r
        INNER JOIN substitutions AS s
            ON s.id = r.id + 1
    )
    -- ================================================ 
    -- selects the formatted string from the last row
    -- in the recursive CTE
    -- ================================================ 
    SELECT formattedDate
    FROM recursiveReplace
    WHERE id = maxval
)

GO