用存储过程生成时间表

利用SQL 的DATEPART函数获取时间元素，通过执行存储过程生成到当前为止的时间序列，仅供参考，各位大侠有更好的做法请多多指教~~~

1. 存储过程：

   USE [DW]
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO

   -- =============================================
   -- Author:  Alex

   -- Create date: 2008-12-23
   -- Description: 创建时间维度
   -- =============================================
   CREATE PROCEDURE pro_creatdimdate
   AS
   BEGIN
   --如果没有日期维度表，先创建，有则跳过。
   IF NOT EXISTS(SELECT NAME FROM sys.tables WHERE NAME = 'Dim_Date')
   BEGIN
   CREATE TABLE [dbo].[Dim_Date](
    [DateKey] [int] NOT NULL,
    [year] [smallint] NULL,
    [quarter] [tinyint] NULL,
    [month] [tinyint] NULL,
    [dayofyear] [smallint] NULL,
    [dayofmonth] [tinyint] NULL,
    [week] [tinyint] NULL,
    [weekday] [tinyint] NULL)
   END
   --

   DECLARE @begindate DATETIME
   SET @begindate = '2007-01-01'
   DECLARE @begindateKey INT
   SET @begindateKey = '20070101'
   DECLARE @enddate DATETIME
   SET @enddate = ISNULL(CAST(CAST((SELECT MAX(DateKey) FROM [dbo].[Dim_Date]) AS VARCHAR) AS DATETIME),DATEDIFF(DAY,1,@begindate))
   DECLARE @DateKey INT,
     @year SMALLINT,
     @quarter TINYINT,
     @month TINYINT,
     @dayofyear SMALLINT,
     @dayofmonth TINYINT,
     @week TINYINT,
     @weekday TINYINT,
     @yearname VARCHAR(4),
     @monthname VARCHAR(2),
     @dayofmonthname VARCHAR(2)
   SET DATEFIRST 1
     
   BEG:
    WHILE(DATEDIFF(DAY,@enddate,GETDATE())>0)
    BEGIN    
     SET @year = DATEPART(year,DATEADD(DAY,1,@enddate))
     SET @quarter = DATEPART(quarter,DATEADD(DAY,1,@enddate))
     SET @month = DATEPART(month,DATEADD(DAY,1,@enddate))
     SET @dayofyear = DATEPART(dayofyear,DATEADD(DAY,1,@enddate))
     SET @dayofmonth = DATEPART(day,DATEADD(DAY,1,@enddate))
     SET @week = DATEPART(week,DATEADD(DAY,1,@enddate))
     SET @weekday = DATEPART(weekday,DATEADD(DAY,1,@enddate))
     SET @yearname = CAST(@year AS VARCHAR)
     SET @monthname = (
         CASE LEN(CAST(@month AS VARCHAR(2)))
         WHEN 1 THEN '0'+CAST(@month AS VARCHAR(2))
         ELSE CAST(@month AS VARCHAR(2))
         END)
     SET @dayofmonthname =(
          CASE LEN(CAST(@dayofmonth AS VARCHAR(2)))
          WHEN 1 THEN '0'+CAST(@dayofmonth AS VARCHAR(2))
          ELSE CAST(@dayofmonth AS VARCHAR(2))
          END)
     SET @DateKey = CAST(CAST(@YEAR AS VARCHAR)+@monthname+@dayofmonthname AS INT)

     INSERT INTO [dbo].[Dim_Date] VALUES(@DateKey,@year,@quarter,@month,@dayofyear,@dayofmonth,@week,@weekday)

     SET @enddate = DATEADD(DAY,1,@enddate)
    GOTO BEG
    END

   END
   GO

2. 在CUBE中新建时间视图获取友好的时间元素

SELECT DateKey, [year], quarter, [month], dayofyear, dayofmonth, week, weekday,
      LEFT(CAST(DateKey AS nvarchar(8)), 4)
      + '年' + SUBSTRING(CAST(DateKey AS nvarchar(8)), 5, 2)
      + '月' + RIGHT(CAST(DateKey AS nvarchar(8)), 2) + '日' AS 日,
      CAST(dayofmonth as nvarchar) + '号' AS 天,
      CASE WHEN dayofmonth <= 10 THEN LEFT(CAST(DateKey AS nvarchar(8)), 4)
      + '年' + SUBSTRING(CAST(DateKey AS nvarchar(8)), 5, 2)
      + '月' + '上旬' WHEN dayofmonth <= 20 THEN LEFT(CAST(DateKey AS nvarchar(8)),
      4) + '年' + SUBSTRING(CAST(DateKey AS nvarchar(8)), 5, 2)
      + '月' + '中旬' ELSE LEFT(CAST(DateKey AS nvarchar(8)), 4)
      + '年' + SUBSTRING(CAST(DateKey AS nvarchar(8)), 5, 2) + '月' + '下旬' END AS 旬,
      CASE WHEN dayofmonth <= 10 THEN LEFT(CAST(DateKey AS nvarchar(8)), 4)
      + '年' + SUBSTRING(CAST(DateKey AS nvarchar(8)), 5, 2)
      + '月' + 'a' WHEN dayofmonth <= 20 THEN LEFT(CAST(DateKey AS nvarchar(8)),
      4) + '年' + SUBSTRING(CAST(DateKey AS nvarchar(8)), 5, 2)
      + '月' + 'b' ELSE LEFT(CAST(DateKey AS nvarchar(8)), 4)
      + '年' + SUBSTRING(CAST(DateKey AS nvarchar(8)), 5, 2) + '月' + 'c' END AS 旬key,       
      LEFT(CAST(DateKey AS nvarchar(8)), 4)
      + '年' + SUBSTRING(CAST(DateKey AS nvarchar(8)), 5, 2) + '月' AS 月,
      LEFT(CAST(DateKey AS nvarchar(8)), 4)
      + '年' + '第' + CAST(quarter AS NVARCHAR(1)) + '季度' AS 季度,
      LEFT(CAST(DateKey AS nvarchar(8)), 4) + '年' AS 年,
      CAST(month as nvarchar) + '月' AS 第N月,
      CASE WHEN dayofmonth <= 10 THEN '上旬' WHEN dayofmonth <= 20 THEN '中旬' ELSE '下旬' END AS 第N旬,
      CASE WHEN dayofmonth <= 10 THEN 'a上旬' WHEN dayofmonth <= 20 THEN 'b中旬' ELSE 'c下旬' END AS 第N旬key,  
      CAST(dayofmonth as nvarchar) + '日' AS 每月第N日,
      CAST(dayofyear as nvarchar) + '日' AS 每年第N日,
      CAST(week as nvarchar) + '周' AS 每年第N周,
      CASE len(rtrim(ltrim(CAST(week AS nvarchar(2)))))
      WHEN 1 THEN LEFT(CAST(DateKey AS nvarchar(8)), 4)
      + '年' + '第0' + rtrim(ltrim(CAST(week AS nvarchar(2))))
      + '周' ELSE LEFT(CAST(DateKey AS nvarchar(8)), 4)
      + '年' + '第' + rtrim(ltrim(CAST(week AS nvarchar(2)))) + '周' END AS 第N周,
      CASE weekday WHEN 2 THEN '星期一' WHEN 3 THEN '星期二' WHEN 4 THEN '星期三'
       WHEN 5 THEN '星期四' WHEN 6 THEN '星期五' WHEN 7 THEN '星期六' WHEN 1 THEN
       '星期日' END AS 星期N
FROM dbo.Dim_Date