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
