So this week I thought we should have a look at one of the many ways to build a date dimension. With that said I want to stress that most of this is borrowed/stolen from the great mssqltips.
The changes I’ve made is just to simplify it a bit, adapt it for my needs and to put it into a procedure. Therefore, since this is a collection of tools primary for me, it has it’s place here.
The script
CREATE PROCEDURE [dbo].[usp_GenerateDateDimension] ( @StartDate date ,@NumberOfYears int ) AS -- Error handling reference: http://www.sommarskog.se/error_handling/Part1.html SET XACT_ABORT, NOCOUNT ON SET DATEFIRST 1; -- Monday SET DATEFORMAT ymd; --SET LANGUAGE US_ENGLISH; -- My system already is DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate); -- Holding table: DROP TABLE IF EXISTS #dim CREATE TABLE #dim ( [DateKey] int PRIMARY KEY, [Date] date, [Day] AS DATEPART(DAY, [date]), [Month] AS DATEPART(MONTH, [date]), [MonthName] AS DATENAME(MONTH, [date]), [Week] AS DATEPART(WEEK, [date]), [ISOweek] AS DATEPART(ISO_WEEK, [date]), [DayOfWeek] AS DATEPART(WEEKDAY, [date]), [Quarter] AS DATEPART(QUARTER, [date]), [Year] AS DATEPART(YEAR, [date]), ); BEGIN TRY BEGIN TRANSACTION INSERT #dim([DateKey], [Date]) SELECT CONVERT(int, CONVERT(nvarchar(12), d, 112), 112) ,d FROM ( SELECT d = DATEADD(DAY, rn - 1, @StartDate) FROM ( SELECT -- Get TOP X rows, the number of dates to generate TOP (DATEDIFF(DAY, @StartDate, @CutoffDate)) rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2 ORDER BY s1.[object_id] ) AS x ) AS y; DROP TABLE IF EXISTS dbo.DateHeader CREATE TABLE dbo.DateHeader ( [DateKey] int NOT NULL PRIMARY KEY, [Date] date NOT NULL, [Day] tinyint NOT NULL, [Weekday] tinyint NOT NULL, [WeekdayName] nvarchar(10) NOT NULL, [IsWeekend] bit NOT NULL, [DayOfYear] smallint NOT NULL, [WeekOfMonth] tinyint NOT NULL, [WeekOfYear] tinyint NOT NULL, [ISOWeekOfYear] tinyint NOT NULL, [Month] tinyint NOT NULL, [MonthName] nvarchar(10) NOT NULL, [Quarter] tinyint NOT NULL, [QuarterName] nvarchar(6) NOT NULL, [Year] int NOT NULL, [YYYY-QQ] nvarchar(7) NOT NULL, [YYYY-MM] nvarchar(7) NOT NULL, ); INSERT INTO dbo.DateHeader WITH (TABLOCKX) SELECT [DateKey] = [DateKey] ,[Date] = [Date] ,[Day] = CONVERT(tinyint, [Day]) ,[Weekday] = CONVERT(tinyint, [DayOfWeek]) ,[WeekDayName] = CONVERT(nvarchar(10), DATENAME(WEEKDAY, [Date])) ,[IsWeekend] = CONVERT(bit, CASE WHEN [DayOfWeek] IN (6, 7) THEN 1 ELSE 0 END) ,[DayOfYear] = CONVERT(smallint, DATEPART(DAYOFYEAR, [date])) ,[WeekOfMonth] = CONVERT(tinyint, DENSE_RANK() OVER ( PARTITION BY [year], [month] ORDER BY [week] )) ,[WeekOfYear] = CONVERT(tinyint, [week]) ,[ISOWeekOfYear] = CONVERT(tinyint, ISOWeek) ,[Month] = CONVERT(tinyint, [month]) ,[MonthName] = CONVERT(nvarchar(10), [MonthName]) ,[Quarter] = CONVERT(tinyint, [quarter]) ,[QuarterName] = CONVERT(nvarchar(6), CASE [quarter] WHEN 1 THEN N'First' WHEN 2 THEN N'Second' WHEN 3 THEN N'Third' WHEN 4 THEN N'Fourth' END) ,[Year] = [year] ,[YYYY-QQ] = CAST([year] AS nvarchar(4)) + '-' + RIGHT( '00' + CAST( CONVERT(tinyint, [quarter]) AS nvarchar(1) ) , (2) ) ,[YYYY-MM] = CAST([year] AS nvarchar(4)) + '-' + RIGHT( '00' + CAST( CONVERT(tinyint, [month]) AS nvarchar(2) ) , (2) ) FROM #dim COMMIT TRANSACTION -- End of transaction END TRY -- End of try BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION -- Error handling reference: -- http://www.sommarskog.se/error_handling/Part1.html EXEC ctr.usp_error_handler RETURN 55555 END CATCH
Output
(Sorry for the wonky formatting above, It’s just to make it fit width-wise.) The output from the query above is a date table called dbo.DateHeader and it looks like this:

And one last time, since I do want to credit when I borrow (steal) from some smart person: Please have a look here (mssqltips) for more details about creating a date dimension. As well as a lot of other tips and tricks.
//mattssok