蛋疼的时候写了一个蛋疼的函数

-- =============================================

-- 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

    )

    -- ================================================

    -- 结果的最后一行就是想要得到的结果

    -- ================================================

    SELECT @string=formattedDate FROM recursiveReplace WHERE id = maxval

    RETURN @string;

END

GO



SELECT  [dbo].[formatDate](GETDATE(), 'YYYY/MM/DD'),

        [dbo].[formatDate](GETDATE(), 'YYMMDD'),

        [dbo].[formatDate](GETDATE(), 'YYYY-MM-DD HH:mm:ss'),

        [dbo].[formatDate](GETDATE(), 'YYYY-MM-DD hh:mm:ss tt')