Sqlserver存储过程生成日期维度

CREATE TABLE [dbo].[Dim_time](
    [the_date] [nvarchar](50) NOT NULL,
    [date_name] [nvarchar](15) NULL,
    [the_year] [int] NULL,
    [year_name] [nvarchar](10) NULL,
    [the_quarter] [nvarchar](10) NULL,
    [quarter_name] [nvarchar](10) NULL,
    [the_month] [int] NULL,
    [month_name] [nvarchar](10) NULL,
    [the_week] [int] NULL,
    [week_name] [nvarchar](10) NULL,
    [week_day] [nvarchar](10) NULL
)

alter PROCEDURE SP_CREATE_TIME_DIMENSION
@begin_date  nvarchar(50)=‘2015-12-01‘ ,
@end_date    nvarchar(50)=‘2015-12-31‘
as
/*
  SP_CREATE_TIME_DIMENSION: 生成时间维数据
  begin_date: 起始时间
  end_date:结束时间
*/
declare 
@dDate date=convert(date,@begin_date),
@v_the_date varchar(10),
@v_the_year varchar(4),
@v_the_quarter varchar(2),
@v_the_month varchar(10),
@v_the_month2 varchar(2),
@v_the_week varchar(2),
@v_the_day varchar(10),
@v_the_day2 varchar(2),
@v_week_day nvarchar(10),
@adddays int=1;
WHILE (@dDate<=convert(date,@end_date))
begin
set @v_the_date=convert(char(10),@dDate,120);--key值
set @v_the_year=DATEPART("YYYY",@dDate);--年
set @v_the_quarter=DATEPART("QQ",@dDate);--季度"
set @v_the_month=DATEPART("MM",@dDate);--月份(字符型)
--set @v_the_month2=to_number(to_char(dDate, ‘mm‘));--月份(数字型)
set @v_the_day=DATEPART("dd",@dDate);--日(字符型)
--set @v_the_day2=to_char(dDate, ‘dd‘);
set @v_the_week=DATEPART("WW",@dDate);--年的第几周
set @v_week_day=DATEPART("DW",@dDate); --星期几
insert into Dim_time(the_date,date_name,the_year,year_name,the_quarter,quarter_name,the_month,month_name,the_week,week_name,week_day)
values(
@v_the_date,convert(nvarchar(10),@v_the_year)+‘年‘+convert(nvarchar(10),@v_the_month)+‘月‘+convert(nvarchar(10),@v_the_day)+‘日‘,
@v_the_year,convert(nvarchar(10),@v_the_year)+‘年‘,
convert(nvarchar(10),@v_the_year)+‘Q‘+convert(nvarchar(10),@v_the_quarter),
convert(nvarchar(10),@v_the_year)+‘年‘+convert(nvarchar(10),@v_the_quarter)+‘季度‘,
convert(int,(convert(nvarchar(10),@v_the_year)+convert(nvarchar(10),@v_the_month))),
convert(nvarchar(10),@v_the_year)+‘年‘+convert(nvarchar(10),@v_the_month)+‘月‘,
@v_the_week
,‘第‘+convert(nvarchar(10),@v_the_week)+‘周‘,
@v_week_day
);
set @dDate=dateadd(day,@adddays,@dDate);
continue
if @dDate=dateadd(day,-1,convert(date,@end_date))
break
end
go

USE [DW]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[SP_CREATE_TIME_DIMENSION]

SELECT    ‘Return Value‘ = @return_value

GO