日历存储过程

---------------------------------------------------------创建表结构

USE [D365ServiceDev_MSCRM]
GO

/****** Object:  Table [dbo].[Dim_time]    Script Date: 6/16/2020 12:27:43 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Dim_time](
    [the_date] [int] NOT NULL,
    [date_name] [nvarchar](30) NULL,
    [the_year] [int] NULL,
    [year_name] [nvarchar](30) NULL,
    [the_quarter] [int] NULL,
    [quarter_name] [nvarchar](30) NULL,
    [the_month] [int] NULL,
    [month_name] [nvarchar](30) NULL,
    [the_week] [int] NULL,
    [week_name] [nvarchar](30) NULL,
    [week_day] [int] NULL,
    [week_day_name] [nvarchar](30) NULL,
    [the_datetime] [datetime] NULL,
 CONSTRAINT [PK_Dim_time] PRIMARY KEY CLUSTERED 
(
    [the_date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

---------------------------------------------------------------------------生成时间数据存储过程-------------------------------------------------

USE [D365ServiceDev_MSCRM]
GO

/****** Object:  StoredProcedure [dbo].[SP_CREATE_TIME_DIMENSION]    Script Date: 6/16/2020 12:29:07 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_CREATE_TIME_DIMENSION]
@begin_date nvarchar(50)='2014-07-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),
@the_datetime datetime,
@adddays int=1;
WHILE (@dDate<=convert(date,@end_date))
begin
set @v_the_date=convert(char(10),@dDate,112);--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,week_day_name,the_datetime)
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)+'年',
@v_the_quarter,
convert(nvarchar(10),@v_the_year)+'年'+convert(nvarchar(10),@v_the_quarter)+'季度',
case when @v_the_month>=10 then
convert(int,(convert(nvarchar(10),@v_the_year)+convert(nvarchar(10),@v_the_month)))
else convert(int,convert(nvarchar(10),@v_the_year)+'0'+convert(nvarchar(10),@v_the_month)) end,
convert(nvarchar(10),@v_the_year)+'年'+convert(nvarchar(10),@v_the_month)+'月',
@v_the_week
,'第'+convert(nvarchar(10),@v_the_week)+'周',
@v_week_day,
case @v_week_day-1
when 1 then '星期一'
when 2 then '星期二'
when 3 then '星期三'
when 4 then '星期四'
when 5 then '星期五'
when 6 then '星期六'
when 0 then '星期日'
else '' end
,@dDate
);
set @dDate=dateadd(day,@adddays,@dDate);
continue
if @dDate=dateadd(day,-1,convert(date,@end_date))
break
end
GO

-------------------------------------------------------------------------------------执行存储过程-----------------------------------------

USE [D365ServiceDev_MSCRM]
GO

DECLARE    @return_value int

EXEC    @return_value = [dbo].[SP_CREATE_TIME_DIMENSION]
        @begin_date = N'2010-01-01',
        @end_date = N'2050-01-01'

SELECT    'Return Value' = @return_value

GO