Let the database generate a detailed calendar
Maybe there is this table, your work will be much easier!
Create Table [DBO]. [Time_ID] [INT] Identity (1, 1) NOT NULL, [THE_DATE] [DATETIME] NULL, [THE_DAY] [NVARCHAR] (15) NULL, [THE_MONTH] [nvarchar] (15) NULL, [THE_YEAR] 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 search @addday = 1 - Date increment (free setting) Select @ddate = '01/01 / 2002 '- Start Date While @ddate <'12 / 31/2004' - End Date 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 @adddaysendgo
Select * from time_dimension
