A quick, and not so dirty, date dimension for SQL Server

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:

What would be returned from the proc above

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

mattssok

Leave a Reply

Your email address will not be published. Required fields are marked *