SQL Server 自定义函数 ft_DateFormats 罗列日期格式化参数和结果

（翻出来以前写的一个函数）

日期格式化参数是魔法数，除了常用的几个根本记不全。看帮助不如直接看结果，一目了然。

IF OBJECT_ID('ft_DateFormats') IS NOT NULL
    DROP FUNCTION ft_DateFormats
GO

CREATE FUNCTION ft_DateFormats (@DATE datetime = NULL)
RETURNS TABLE
AS
RETURN (
    WITH style(style1,style2) AS (
        SELECT t1.number, t2.number
          FROM (
                SELECT number
                  FROM master..spt_values
                 WHERE type = 'p'
                   AND (   number BETWEEN 0 AND 14
                        OR number BETWEEN 20 AND 25
                       )
               ) t1
     FULL JOIN (
                SELECT number
                  FROM master..spt_values
                 WHERE type = 'p'
                   AND (   number BETWEEN 100 AND 114
                        OR number BETWEEN 120 AND 121
                        OR number BETWEEN 126 AND 127
                        OR number BETWEEN 130 AND 131
                       )
               ) t2
            ON t1.number + 100 = t2.number
    )
    SELECT style1, Convert(nvarchar(30), ISNULL(@DATE,GetDate()), style1) AS text1,
           style2, Convert(nvarchar(30), ISNULL(@DATE,GetDate()), style2) AS text2
      FROM style
)
GO

使用举例

SELECT * FROM ft_DateFormats(NULL)

结果

     style1 text1                               style2 text2
----------- ------------------------------ ----------- ------------------------------
          0 04  3 2015 11:24AM                     100 04  3 2015 11:24AM
          1 04/03/15                               101 04/03/2015
          2 15.04.03                               102 2015.04.03
          3 03/04/15                               103 03/04/2015
          4 03.04.15                               104 03.04.2015
          5 03-04-15                               105 03-04-2015
          6 03 04 15                               106 03 04 2015
          7 04 03, 15                              107 04 03, 2015
          8 11:24:27                               108 11:24:27
          9 04  3 2015 11:24:27:873AM              109 04  3 2015 11:24:27:873AM
         10 04-03-15                               110 04-03-2015
         11 15/04/03                               111 2015/04/03
         12 150403                                 112 20150403
         13 03 04 2015 11:24:27:873                113 03 04 2015 11:24:27:873
         14 11:24:27:873                           114 11:24:27:873
         20 2015-04-03 11:24:27                    120 2015-04-03 11:24:27
         21 2015-04-03 11:24:27.873                121 2015-04-03 11:24:27.873
         22 04/03/15 11:24:27 AM                  NULL NULL
         23 2015-04-03                            NULL NULL
         24 11:24:27                              NULL NULL
         25 2015-04-03 11:24:27.873               NULL NULL
       NULL NULL                                   126 2015-04-03T11:24:27.873
       NULL NULL                                   127 2015-04-03T11:24:27.873
       NULL NULL                                   130 14 جمادى الثانية 1436 11:24:27
       NULL NULL                                   131 14/06/1436 11:24:27:873AM