When creating a data warehouse I ran into the concept of the Date Dimension (used for housing a giant list of dates and corresponding dateparts and segments of time) and created a segment of code for a table that I was able to use to date. To utilize this code we must first create the table in which the data will live in:
CREATE TABLE [dbo].[DimDate](
[DateKey] INT NOT NULL
,[FullDate] DATETIME NOT NULL
,[Year] INT NOT NULL
,[Quarter] INT NOT NULL
,[Month] INT NOT NULL
,[MonthName] VARCHAR(30) NOT NULL
,[Week] INT NOT NULL
,[WeekOfMonth] INT NOT NULL
,[DayName] VARCHAR(30) NOT NULL
,[DayofWeek] INT NOT NULL
,[DayofMonth] INT NOT NULL
,[IsWeekend] BIT NOT NULL
) ON [PRIMARY];
GO
As you can see the above table will create columns with the names DateKey (which will have a unique constraint placed on it later), FullDate, Year, Quarter, etc. These columns gave me everything I needed so feel free to add more and create your own relative formulas to compute them. Once this table is created we can go ahead and write a stored procedure that will delete all data in the table and repopulate it with dates within a certain date range:
CREATE PROCEDURE [dbo].[spPopulateDimDate] @StartDate DATE, @EndDate DATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MonthOffset INT = 3,
@FirstDate DATE
WHILE (@StartDate <= @EndDate)
BEGIN
SET @FirstDate = DATEADD(MONTH, -1 * @MonthOffset, @StartDate)
INSERT INTO [dbo].[DimDate]
(
[DateKey],
[FullDate],
[Year],
[Quarter],
[Month],
[MonthName],
[Week],
[WeekOfMonth],
[DayName],
[DayOfWeek],
[DayOfMonth],
[IsWeekend]
)
SELECT
[DateKey] = CONVERT(INT,CONVERT(VARCHAR(20), @StartDate, 112)),
[FullDate] = @StartDate,
[Year] = DATEPART(YEAR,@StartDate),
[Quarter] = DATEPART(QUARTER, @StartDate),
[Month] = DATEPART(MONTH, @StartDate),
[MonthName] = DATENAME(MONTH, @StartDate),
[Week] = CASE WHEN DATEPART(DAY, @StartDate) BETWEEN 1 AND 7 THEN 1
WHEN DATEPART(DAY, @StartDate) BETWEEN 8 AND 14 THEN 2
WHEN DATEPART(DAY, @StartDate) BETWEEN 15 AND 21 THEN 3
WHEN DATEPART(DAY, @StartDate) BETWEEN 22 AND 28 THEN 4
WHEN DATEPART(DAY, @StartDate) > 28 THEN 5
END,
[WeekOfMonth] = + CONVERT(VARCHAR(20),DATEPART(WEEK, @StartDate) – DATEPART(WEEK, CONVERT(CHAR(6), @StartDate, 112) + ’01’) + 1),
[DayName] = DATENAME(WEEKDAY, @StartDate),
[DayOfWeek] = DATEPART(WEEKDAY, @StartDate),
[DayOfMonth] = DATEPART(DAY, @StartDate),
[IsWeekend] = CASE WHEN DATENAME(WEEKDAY, @StartDate) IN (‘Saturday’, ‘Sunday’) THEN 1 ELSE 0 END
SET @StartDate = DATEADD(DAY, 1, @StartDate)
END
END
GO
The beginning portion will check to see if the stored procedure exists and, if so, will erase all data and begin to populate the table with new data within the specified date range (this is done via parameters for the SP). It then creates a WHILE loop to loop through each day, aggreagate, and populate based off of the date. Once this is done then that’s it! It will even tell you which days are weekends!
That One SQL Blog 