sqlserver日历表

发布时间：2020-12-12 14:01:41 所属栏目：MsSql教程来源：网络整理

导读：CREATE TABLE [dbo].[time_dimension] ( ? ?[time_id] [int] IDENTITY (1,1) NOT NULL,? ?[the_date] [datetime] NULL,? ?[the_day] [nvarchar] (15) NULL,? ?[the_month] [nvarchar] (15) NULL,? ?[the_year] [smallint] NULL,? ?[day_of_month] [smallint]

CREATE TABLE [dbo].[time_dimension] ( ? ?[time_id] [int] IDENTITY (1,1) NOT NULL,? ?[the_date] [datetime] NULL,? ?[the_day] [nvarchar] (15) NULL,? ?[the_month] [nvarchar] (15) NULL,? ?[the_year] [smallint] NULL,? ?[day_of_month] [smallint] NULL,? ?[week_of_year] [smallint] NULL,? ?[month_of_year] [smallint] NULL,? ?[quarter] [nvarchar] (2) NULL,? ?[fiscal_period] [nvarchar] (20) NULL ) ON [PRIMARY] DECLARE @WeekString varchar(12),@dDate SMALLDATETIME,@sMonth varchar(20),@iYear smallint,@iDayOfMonth smallint,@iWeekOfYear smallint,@iMonthOfYear smallint,@sQuarter varchar(2),@sSQL varchar(100),@adddays int ? ? SELECT @adddays = 1 --日期增量（可以自由设定） SELECT @dDate = '04/05/2013' --开始日期 ? ? WHILE @dDate < '12/31/2023' ?--结束日期 BEGIN ? ? ? ?SELECT @WeekString = DATENAME (dw,@dDate) ? ?SELECT @sMonth=DATENAME(mm,@dDate) ? ?SELECT @iYear= DATENAME (yy,@dDate) ? ?SELECT @iDayOfMonth=DATENAME (dd,@dDate) ? ?SELECT @iWeekOfYear= DATENAME (week,@dDate) ? ?SELECT @iMonthOfYear=DATEPART(month,@dDate) ? ?SELECT @sQuarter = 'Q' + ?CAST(DATENAME (quarter,@dDate)as varchar(1)) ? ?INSERT INTO time_dimension(the_date,the_day,the_month,the_year,? ?day_of_month,? ?week_of_year,month_of_year,quarter) VALUES ? ?(@dDate,@WeekString,@sMonth,@iYear,@iDayOfMonth,@iWeekOfYear,? ?@iMonthOfYear,@sQuarter) ? ?SELECT @dDate = @dDate + @adddays END GO select * from time_dimension

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!