生成日历表代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】生成日历表

作者:奕男 / 发布于2016/9/14/ 1121

GO
SET QUOTED_IDENTIFIER ON
GO
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]
GO

DECLARE @WeekString varchar(12), 
@dDate Datetime, 
@sMonth varchar(20), 
@iYear smallint, 
@iDayOfMonth smallint, 
@iWeekOfYear smallint, 
@iMonthOfYear smallint, 
@sQuarter varchar(2), 
@sSQL varchar(100), 
@adddays int 
   
SELECT @adddays = 1 --日期增量
SELECT @dDate = '01/01/2000' --开始日期 
   
WHILE @dDate <= '12/31/2200'  --结束日期 
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' +  DATENAME (quarter, @dDate)
   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

试试其它关键字

同语言下

可能有用的

奕男贡献的其它代码(13)