-- =============================================
-- Author:      小爱 
-- Create date: 2012-03-26
-- Description: 以不同的格式显示日期/时间数据 
-- @date:       合法的日期
-- @format:     规定日期/时间的输出格式
-- =============================================

IF OBJECT_ID('formatDate','FN') IS NOT NULL DROP FUNCTION [formatDate]
GO

CREATE FUNCTION [dbo].[formatDate](@date AS datetime, @format varchar(50))
RETURNS varchar(50)
AS
BEGIN 
    DECLARE @string varchar(50)
    -- ================================================
    -- 填充日期/时间的输出格式
    -- ================================================
    ;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, 'MM',   RIGHT('00' + CAST(MONTH(@date) AS varchar(2)),2)
            UNION ALL SELECT  5, 'M',    CAST(MONTH(@date) AS varchar(2))
            UNION ALL SELECT  6, 'DD',   RIGHT('00' + CAST(DAY(@date) AS varchar(2)),2)
            UNION ALL SELECT  7, 'D',    CAST(DAY(@date) AS varchar(2))
            UNION ALL SELECT  8, 'HH',   RIGHT('00' + CAST(DATEPART(hour,@date) AS varchar(2)),2)
            UNION ALL SELECT  9, 'H',    CAST(DATEPART(hour,@date) AS varchar(2))
            UNION ALL SELECT 10, 'hh',   RIGHT('00' + CAST(DATEPART(hour, @date) - (12 * CEILING((DATEPART(hour, @date) - 12)*.1)) AS varchar(2)),2)
            UNION ALL SELECT 11, 'h',    CAST(DATEPART(hour, @date) - (12 * CEILING((DATEPART(hour, @date) - 12)*.1)) AS varchar(2))
            UNION ALL SELECT 12, 'mm',   RIGHT('00' + CAST(DATEPART(minute,@date) AS varchar(2)),2)
            UNION ALL SELECT 13, 'm',    CAST(DATEPART(minute,@date) AS varchar(2))
            UNION ALL SELECT 14, 'ss',   RIGHT('00' + CAST(DATEPART(second,@date) AS varchar(2)),2)
            UNION ALL SELECT 15, 's',    CAST(DATEPART(second,@date) AS varchar(2))
            UNION ALL SELECT 16, 'fff',  RIGHT('000' + CAST(DATEPART(millisecond,@date) AS varchar(3)),3)
            UNION ALL SELECT 17, 'f',    CAST(DATEPART(millisecond,@date) AS varchar(3))
            UNION ALL SELECT 18, 'tt',   CASE WHEN DATEPART(hour,@date) >= 12 THEN 'PM' ELSE 'AM' END
            UNION ALL SELECT 19, 't',    CASE WHEN DATEPART(hour,@date) >= 12 THEN 'P' ELSE 'A' END
        ) AS susbst (id, code, value)
    ),
    -- ================================================
    -- 对列表进行格式化处理 
    -- ================================================
    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
    ), 
    -- ================================================
    -- 使字符串区分大小写
    -- ================================================
    formatStrings (formatString) AS (
        SELECT @format COLLATE Latin1_General_CS_AS
    ),
    -- ================================================
    -- 使用CTE递归替换标记 
    -- ================================================
    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
    )
    -- ================================================
    -- 结果的最后一行就是想要得到的结果