formatDate_Islands_iTVF.sql #blog

formatDate_Islands_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].[formatDateWithIslands](@date AS datetime, @format_string varchar(50))
RETURNS TABLE
AS
RETURN (
    WITH 
    -- ================================================ 
    -- Build 4 rows
    -- ================================================ 
    fourRows (N) AS (
        SELECT 1
        UNION ALL 
        SELECT 2
        UNION ALL 
        SELECT 3
        UNION ALL 
        SELECT 4
    ),
    -- ================================================ 
    -- build a small 64 rows tally table
    -- ================================================ 
    cteTally (N) AS (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM fourRows AS A
        CROSS JOIN fourRows AS B
        CROSS JOIN fourRows AS C
    ),
    -- ================================================ 
    -- create the allowedTokens list
    -- ================================================ 
    allowedTokens (code, value) AS (
        SELECT code COLLATE Latin1_General_CS_AS, value 
        FROM (
                      SELECT 'YYYY', RIGHT('0000' + CAST(YEAR(@date) AS varchar(4)),4)
            UNION ALL SELECT 'YY',   RIGHT('00' + CAST(YEAR(@date) AS varchar(4)),2) 
            UNION ALL SELECT 'Y',    CAST(CAST(RIGHT('00' + CAST(YEAR(@date) AS varchar(4)),2) AS int) AS varchar(2))
            UNION ALL SELECT 'MMMM', DATENAME(month, @date)
            UNION ALL SELECT 'MM',   RIGHT('00' + CAST(MONTH(@date) AS varchar(2)),2)
            UNION ALL SELECT 'M',    CAST(MONTH(@date) AS varchar(2))
            UNION ALL SELECT 'DDDD', DATENAME(weekday, @date)
            UNION ALL SELECT 'DD',   RIGHT('00' + CAST(DAY(@date) AS varchar(2)),2)
            UNION ALL SELECT 'D',    CAST(DAY(@date) AS varchar(2))
            UNION ALL SELECT 'HH',   RIGHT('00' + CAST(DATEPART(hour,@date) AS varchar(2)),2)
            UNION ALL SELECT 'H',    CAST(DATEPART(hour,@date) AS varchar(2))
            UNION ALL SELECT 'hh',   RIGHT('00' + CAST(CASE DATEPART(hour, @date) WHEN 12 THEN 12 ELSE DATEPART(hour, @date) % 12 END AS varchar(2)),2)
            UNION ALL SELECT 'h',    CAST(DATEPART(hour, @date) - (12 * CEILING((DATEPART(hour, @date) - 12)*.1)) AS varchar(2))
            UNION ALL SELECT 'mm',   RIGHT('00' + CAST(DATEPART(minute,@date) AS varchar(2)),2)
            UNION ALL SELECT 'm',    CAST(DATEPART(minute,@date) AS varchar(2))
            UNION ALL SELECT 'ss',   RIGHT('00' + CAST(DATEPART(second,@date) AS varchar(2)),2)
            UNION ALL SELECT 's',    CAST(DATEPART(second,@date) AS varchar(2))
            UNION ALL SELECT 'fff',  RIGHT('000' + CAST(DATEPART(millisecond,@date) AS varchar(3)),3)
            UNION ALL SELECT 'f',    CAST(DATEPART(millisecond,@date) AS varchar(3))
            UNION ALL SELECT 'tt',   CASE WHEN DATEPART(hour,@date) >= 12 THEN 'PM' ELSE 'AM' END
            UNION ALL SELECT 't',    CASE WHEN DATEPART(hour,@date) >= 12 THEN 'P' ELSE 'A' END
        ) AS susbst (code, value)
    ),
    -- ================================================ 
    -- make the parameter case-sensitive
    -- ================================================ 
    formatStringCS (format_string) AS (
        SELECT @format_string COLLATE Latin1_General_CS_AS 
    ),
    -- ================================================ 
    -- join the format string to the tally table
    -- and add some rankings to identify the "islands"
    -- ================================================ 
    tokenizedString AS (
        SELECT *, 
            groupId = DENSE_RANK() OVER(ORDER BY C, _groupId)
        FROM (
            SELECT N, 
                SUBSTRING(format_string, N, 1) AS C,
                _groupId = 
                    N - ROW_NUMBER() OVER (
                        PARTITION BY SUBSTRING(format_string, N, 1) 
                        ORDER BY N
                    )
            FROM cteTally, formatStringCS
            WHERE N <= LEN(@format_string)
        ) AS fs
    ),
    -- ================================================ 
    -- Aggregate groups
    -- ================================================ 
    groupedTokens AS (
        SELECT groupId, 
            groupLen = COUNT(*),
            groupMin = MIN(N),
            C = MIN(C)
        FROM tokenizedString
        GROUP BY groupId
    )
    -- ================================================ 
    -- Join to tokens and allowedTokens
    -- Uses the count inside the group to join to the
    -- susbstitutions 
    -- Uses the groupMin to sort the output
    -- Concatenate the results using FOR XML PATH('')
    -- Uses .value to eliminate XML entities escape
    -- ================================================ 
    SELECT formattedDate = (
        SELECT (
    
            SELECT ISNULL(SUB.value, C)
            FROM groupedTokens AS FS
            LEFT JOIN allowedTokens AS SUB
                ON REPLICATE(FS.C,groupLen) = SUB.code
            ORDER BY groupMin
            FOR XML PATH(''), TYPE
        ).value('(./text())[1]','nvarchar(50)') 
    )
)