sqlserver 生成年月周日

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 = ‘01/01/2016‘ --开始日期
  
WHILE @dDate < ‘12/31/2019‘  --结束日期
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

（编辑：李大同）

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