创建日期时间维度数据-CSDN博客

方式一、

1建表：

CREATE TABLE [dbo].[Dim_Date](
	[DateKey] [int] NULL,					--日期编号
	[Date] [datetime] NULL,					--日期
	[Year] [float] NULL,					--年
	[Month] [float] NULL,					--月
	[Month EN] [nvarchar](50) NULL,			--月【英文表示】
	[Month Short EN] [nvarchar](50) NULL,	--月【英文表示简称】
	[Month CN] [nvarchar](50) NULL,			--月【中文表示】
	[Day] [float] NULL,						--天
	[Quarter] [float] NULL,					--季度
	[Quarter EN] [nvarchar](50) NULL,		--季度【英文表示】	
	[Quarter CN] [nvarchar](50) NULL,		--季度【中文表示】
	[Weekday] [float] NULL,					--周
	[Weekday CN] [nvarchar](50) NULL,		--周【中文表示】
	[Weekday Short EN] [nvarchar](50) NULL,	--周【英文简称表示】
	[Week of Year] [float] NULL,			--一年中的第几周
	[Day of Year] [float] NULL,				--一年中的第几天
	[SemiYearly] [nvarchar](50) NULL,		--半年
	[Period of Ten Days] [nvarchar](10) NULL,--旬【中文表示】
	[Period of Index] [nvarchar](2) NULL,	--旬【英文表示】
	[Weekend] [nvarchar](5) NULL			--工作日
) ON [PRIMARY]

GO

2.建存储过程

SET DATEFIRST 7 --设周日为每周的第一天
GO
--向日期表插入数据
DECLARE @b1 DATETIME
set @b1='2000-01-01'     --设置起始日期 
 
WHILE @b1<'2014-01-01'   --设置截止日期
BEGIN
 INSERT INTO dbo.[Dim_Date] (
  [DateKey],
  [Date], 
  [Year],
  [Month],
  [Month EN],
  [Month Short EN],
  [Month CN],
  [Day],
  [Quarter],
  [Quarter EN],
  [Quarter CN],
  [Weekday],
  [Weekday CN],
  [Weekday Short EN],
  [Week of Year],
  [Day of Year],
  [SemiYearly],
  [Period of Ten Days],
  [Period of Index] ,
  [Weekend]
 ) 
 VALUES( 
   CONVERT(NVARCHAR(10),@b1,112),  --DateKey 1
   @b1, --Date 2
   DATEPART(year, @b1), --Year 3
   DATEPART(month, @b1), --Month 4
   CASE --Month EN 5
     when (DATEPART(month, @b1))='1' then 'January' 
     when (DATEPART(month, @b1))='2' then 'February'
     when (DATEPART(month, @b1))='3' then 'March'
     when (DATEPART(month, @b1))='4' then 'April'
     when (DATEPART(month, @b1))='5' then 'May'
     when (DATEPART(month, @b1))='6' then 'June'
     when (DATEPART(month, @b1))='7' then 'July'
     when (DATEPART(month, @b1))='8' then 'August'
     when (DATEPART(month, @b1))='9' then 'September'
     when (DATEPART(month, @b1))='10' then 'October'
     when (DATEPART(month, @b1))='11' then 'November'
     else 'December'
   END, 
   CASE --Month Short En 6
    when (DATEPART(month, @b1))='1' then 'Jan' 
    when (DATEPART(month, @b1))='2' then 'Feb'
    when (DATEPART(month, @b1))='3' then 'Mar'
    when (DATEPART(month, @b1))='4' then 'Apr'
    when (DATEPART(month, @b1))='5' then 'May'
    when (DATEPART(month, @b1))='6' then 'Jun'
    when (DATEPART(month, @b1))='7' then 'Jul'
    when (DATEPART(month, @b1))='8' then 'Aug'
    when (DATEPART(month, @b1))='9' then 'Sep'
    when (DATEPART(month, @b1))='10' then 'Oct'
    when (DATEPART(month, @b1))='11' then  'Nov'
    else 'Dec'
   END,
      CASE --Month CN 7
       when (DATEPART(month, @b1))='1' then N'一月' 
    when (DATEPART(month, @b1))='2' then N'二月'
    when (DATEPART(month, @b1))='3' then N'三月'
    when (DATEPART(month, @b1))='4' then N'四月'
    when (DATEPART(month, @b1))='5' then N'五月'
    when (DATEPART(month, @b1))='6' then N'六月'
    when (DATEPART(month, @b1))='7' then N'七月'
    when (DATEPART(month, @b1))='8' then N'八月'
    when (DATEPART(month, @b1))='9' then N'九月'
    when (DATEPART(month, @b1))='10' then N'十月'
    when (DATEPART(month, @b1))='11' then  N'十一月'
    else N'十二月'
   END,
   DATEPART(day, @b1),--day  8
   DATEName (qq, @b1),--quarter 9
   CASE   --quarter en  10
    when DATEName (qq, @b1)='1' then 'Q1'
    when DATEName (qq, @b1)='2' then 'Q2' 
    when DATEName (qq, @b1)='3' then 'Q3'
    else  'Q4'
   END,
         CASE  --quarter cn  11
    when DATEName (qq, @b1)='1' then N'一季度'
    when DATEName (qq, @b1)='2' then N'二季度' 
    when DATEName (qq, @b1)='3' then N'三季度'
    else  N'四季度'
   END,    
   DATEPART(dw, @b1),--Weekday 12
   CASE --Weekday CN  13
    when DATEPART(dw, @b1)=1 then  N'星期日'
    when DATEPART(dw, @b1)=2 then  N'星期一'
    when DATEPART(dw, @b1)=3 then  N'星期二'
    when DATEPART(dw, @b1)=4 then  N'星期三'
    when DATEPART(dw, @b1)=5 then  N'星期四'
    when DATEPART(dw, @b1)=6 then  N'星期五'  
    else N'星期六'
   END,
   CASE --Weekday Short EN 14  --注意,周日是第一天.
    when DATEPART(dw, @b1)='1' then 'Sun'
    when DATEPART(dw, @b1)='2' then 'Mon'
    when DATEPART(dw, @b1)='3' then 'Tue'
    when DATEPART(dw, @b1)='4' then 'Wed'
    when DATEPART(dw, @b1)='5' then 'Thu'
    when DATEPART(dw, @b1)='6' then 'Fri'
    else 'Sat'
   END, 
   DATEName (wk, @b1),--week of year 15
   DATEName (dy, @b1),--day of year  16
   CASE --SemiYearly 17
    when DATEPART(month, @b1)<=6 then N'上半年'
    else N'下半年'
   END,
      CASE  --Period of Ten Days 18
    when DATEName (dd, @b1)<=10 then N'上旬' 
    when DATEName (dd, @b1)>20  then N'下旬'
    else N'中旬'
   END,
      CASE  --Period of Ten Days 19
    when DATEName (dd, @b1)<=10 then N'1' 
    when DATEName (dd, @b1)>20  then N'3'
    else N'2'
   END,
   CASE --Is it Weekend? 20
    when DATEPART(dw, @b1)='1' then '周末'
    when DATEPART(dw, @b1)='7' then '周末'
    else '平时'
   END 
)
--日期加1天
 set @b1=DATEADD(day, 1, @b1)
END
GO

方式二、

-- 上面生成日期，下边用视图生成年、半年、季、月、日等
go
create table tbl_MetaData_Date
(
[Date] [datetime] NOT NULL,
[Ten_Days] [datetime] NULL,
[Half_Year] [datetime] NULL
) ON [PRIMARY]
go

create view v_metadata_date
as
select date, year(date) as year, month(date) as month, day(date) as day, case when datepart(weekday, date) = 1 then datediff(wk, convert(datetime,
dateadd(yy, datediff(yy, 0, date), 0)), date) when datepart(weekday, date) > 1 then datediff(wk, convert(datetime, dateadd(yy, datediff(yy, 0,
date), 0)), date) + 1 end as week, { fn quarter(date) } as quarter, ten_days, half_year, date as dayne
from tbl_MetaData_Date
go

转载于:https://www.cnblogs.com/emmy/archive/2011/04/26/2029562.html