I am looking to populate a date dimension table in a SQL Server 2008 database. The fields in the table are as follows:

[DateId]                    INT IDENTITY(1,1) PRIMARY KEY
[DateTime]                  DATETIME
[Date]                      DATE
[DayOfWeek_Number]          TINYINT
[DayOfWeek_Name]            VARCHAR(9)
[DayOfWeek_ShortName]       VARCHAR(3)
[Week_Number]               TINYINT
[Fiscal_DayOfMonth]         TINYINT
[Fiscal_Month_Number]       TINYINT
[Fiscal_Month_Name]         VARCHAR(12)
[Fiscal_Month_ShortName]    VARCHAR(3)
[Fiscal_Quarter]            TINYINT     
[Fiscal_Year]               INT
[Calendar_DayOfMonth]       TINYINT
[Calendar_Month Number]     TINYINT     
[Calendar_Month_Name]       VARCHAR(9)
[Calendar_Month_ShortName]  VARCHAR(3)
[Calendar_Quarter]          TINYINT
[Calendar_Year]             INT
[IsLeapYear]                BIT
[IsWeekDay]                 BIT
[IsWeekend]                 BIT
[IsWorkday]                 BIT
[IsHoliday]                 BIT
[HolidayName]               VARCHAR(255)

I have written a function DateListInRange(D1,D2) that returns all the dates between two parameter dates D1 and D2 inclusive.

ie. parameters '2014-01-01' and '2014-01-03' would return:


I want to populate the DATE_DIM table for all dates within a range, i.e. 2010-01-01 to 2020-01-01. Most of the fields can be populated with the SQL 2008 DATEPART, DATENAME, and YEAR functions.

The fiscal data contains slightly more logic, some of which is dependant on each other. For example: Fiscal quarter 1 -> Fiscal month must be 1, 2 or 3 Fiscal quarter 2 -> Fiscal month must be 4, 5 or 6

I can easily write a table valued function that accepts a specific date, and then outputs all of the fiscal data, or ALL of the fields even. Then I would just need this function to run on each row of the DateListInRange function.

I am not highly concerned with speed as this will only need to be populated a few times a year when the table of holidays is altered.

What is the best way to write this in SQL?

Currently its like this:

    CAST([Date] AS DATE)                AS [Date],
    DATEPART(W,[Date])                  AS [DayOfWeek_Number], -- First day of week is sunday
    DATENAME(W,[Date])                  AS [DayOfWeek_Name],
    SUBSTRING(DATENAME(DW,[Date]),1,3)  AS [DayOfWeek_ShortName],
    DATEPART(WK, [Date])                AS [WeekNumber],
    DATEPART(M, [Date])                 AS [Calendar_Month_Number],
    DATENAME(M, [Date])                 AS [Calendar_Month_Name],
    SUBSTRING(DATENAME(M, [Date]),1,3)  AS [Calendar_Month_ShortName],
    DATEPART(QQ, [Date])                AS [Calendar_Quarter],
    YEAR([Date])                        AS [Calendar_Year],

        (YEAR([Date]) % 4 = 0) AND (YEAR([Date]) % 100 != 0) 
        (YEAR([Date]) % 400 = 0)
    THEN 1 ELSE 0 
    END                                     AS [IsLeapYear],

        DATEPART(W,[Date]) = 1 OR DATEPART(W,[Date]) = 7
    THEN 0 ELSE 1
    END                                     AS [IsWeekDay]
FROM [DateListForRange] 

If I do the same for the fiscal data there will be quite a bit of repetition in each case statement would could be avoided using a function and maybe cross applying the TVF over the list of dates.

Please note I am using SQL Server 2008 so a lot of newer date functionality is minimal.


2 Answers 2


UPDATE: for a more generic example of creating and populating a calendar or dimension table, see this tip:

For the specific question at hand, here's my attempt. I will update this with the magic you use to determine things like Fiscal_MonthNumber and Fiscal_MonthName, because right now they're the only non-intuitive part of your question, and it's the only tangible information you actually didn't include.

The "best" (read:most efficient) way to populate a calendar table, IMHO, is to use a set, rather than a loop. And you can generate this set without burying logic into user-defined functions, which really don't gain you anything but encapsulation - otherwise it's just another object to maintain. I talk about this in a lot more detail in this blog series:

If you want to keep using your function, make sure it's not a multi-statement table-valued function; that's not going to be efficient at all. You want to make sure that it is inline (e.g. has a single RETURN statement and no explicit @table declaration), has WITH SCHEMABINDING, and doesn't use recursive CTEs. Outside of a function, here is how I would do it:

CREATE TABLE dbo.DateDimension
  [Date]                      DATE PRIMARY KEY,
  [DayOfWeek_Number]          TINYINT,
  [DayOfWeek_Name]            VARCHAR(9),
  [DayOfWeek_ShortName]       VARCHAR(3),
  [Week_Number]               TINYINT,
  [Fiscal_DayOfMonth]         TINYINT,
  [Fiscal_Month_Number]       TINYINT,
  [Fiscal_Month_Name]         VARCHAR(12),
  [Fiscal_Month_ShortName]    VARCHAR(3),
  [Fiscal_Quarter]            TINYINT,     
  [Fiscal_Year]               SMALLINT,
  [Calendar_DayOfMonth]       TINYINT,
  [Calendar_Month Number]     TINYINT,     
  [Calendar_Month_Name]       VARCHAR(9),
  [Calendar_Month_ShortName]  VARCHAR(3),
  [Calendar_Quarter]          TINYINT,
  [Calendar_Year]             SMALLINT, 
  [IsLeapYear]                BIT,
  [IsWeekDay]                 BIT,
  [IsWeekend]                 BIT,
  [IsWorkday]                 BIT,
  [IsHoliday]                 BIT,
  [HolidayName]               VARCHAR(255)
-- add indexes, constraints, etc.

With the table in place, you can perform a single, set-based insert of as many years of data as you want from whatever start date you choose. Just specify the start date and the number of years. I use a "stacked CTE" technique to avoid redundancy and only perform a whole slew of calculations once; the output columns from the earlier CTEs are then subsequently used in further calculations later on.

-- these are important:

DECLARE @start DATE = '20100101', @years TINYINT = 20;

;WITH src AS
  -- you don't need a function for this...
  SELECT TOP (DATEDIFF(DAY, @start, DATEADD(YEAR, @years, @start)))
    d = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY s1.number)-1, @start)
   FROM master.dbo.spt_values AS s1
   CROSS JOIN master.dbo.spt_values AS s2
   -- your own numbers table works much better here, but this'll do
w AS 
  SELECT d, 
    wd      = DATEPART(WEEKDAY,d), 
    wdname  = DATENAME(WEEKDAY,d), 
    wnum    = DATEPART(ISO_WEEK,d),
    qnum    = DATEPART(QUARTER, d),
    y       = YEAR(d),
    m       = MONTH(d),
    mname   = DATENAME(MONTH,d),
    md      = DAY(d)
  FROM src
q AS
  SELECT *, 
    wdsname   = LEFT(wdname,3),
    msname    = LEFT(mname,3),
    IsWeekday = CASE WHEN wd IN (1,7) THEN 0 ELSE 1 END,
  FROM w
q1 AS
  SELECT *, 
    -- useless, just inverse of IsWeekday, but okay:
    IsWeekend = CASE WHEN IsWeekday = 1 THEN 0 ELSE 1 END,
         + CASE WHEN md >= 26 AND m%3 = 0 THEN 2 ELSE 1 END,0),4)
    FROM q
--INSERT dbo.DimWithDateAllPersisted(Date)
  DateKey = d,
  DayOfWeek_Number = wd,
  DayOfWeek_Name = wdname,
  DayOfWeek_ShortName = wdsname,
  Week_Number = wnum,
  -- I'll update these four lines when I have usable info
  Fiscal_DayOfMonth      = 0,--'?magic?',
  Fiscal_Month_Number    = 0,--'?magic?',
  Fiscal_Month_Name      = 0,--'?magic?',
  Fiscal_Month_ShortName = 0,--'?magic?',
  Fiscal_Quarter = fq,
  Fiscal_Year = CASE WHEN fq = 4 AND m < 3 THEN y-1 ELSE y END,
  Calendar_DayOfMonth = md,
  Calendar_Month_Number = m,
  Calendar_Month_Name = mname,
  Calendar_Month_ShortName = msname,
  Calendar_Quarter = qnum,
  Calendar_Year = y,
  IsLeapYear = CASE 
    WHEN (y%4 = 0 AND y%100 != 0) OR (y%400 = 0) THEN 1 ELSE 0 END,
  IsWorkday = CASE WHEN IsWeekday = 1 THEN 1 ELSE 0 END,
  IsHoliday = 0,
  HolidayName = ''
FROM q1;

Now, you still have these "holiday" and "workday" columns left to deal with - this gets a little more cumbersome, but you need to update those three columns with any holidays that appear in your date range. Things like Christmas Day are really easy:

UPDATE dbo.DateDimension
  SET IsWorkday = 0, IsHoliday = 1, HolidayName = 'Christmas'
  WHERE Calendar_Month_Number = 12 AND Calendar_DayOfMonth = 25;

Things like Easter get a lot trickier - I have blogged some ideas here many years ago.

And of course your company non-workdays that have absolutely nothing to do with public holidays etc. must be updated directly by you - SQL Server isn't going to have some built-in way to know your company's calendar.

Now, I purposely stayed away from computing any of these columns, because you said something like the end users have previously preferred fields they can drag and drop - I'm not sure if the end users really know or care if the source of a column is a real column, a computed column, or comes from a view, query or function...

Assuming you do want to look into computing some of these columns to ease on your maintenance (and persist them to pay storage for query speed), you can look into that. However, just as a warning, some of these columns can't be defined as computed and persisted because they are non-deterministic. Here's one example, and how to get around it.



Msg 4936, Level 16, State 1, Line 130
Computed column 'DayOfWeek_Number' in table 'Test' cannot be persisted because the column is non-deterministic.

The reason this can't be persisted is because many date-related functions rely on the session settings of the user, like DATEFIRST. SQL Server can't persist the above column because DATEPART(WEEKDAY should give different results - given the same data - for two different users who happen to have different DATEFIRST settings.

Then you might get clever, and say, well, I can set it to be the number of days, modulo 7, offset from some day I know to be a Saturday (say, '2000-01-01'). So you try:

  DayOfWeek_Number AS 
    COALESCE(NULLIF(DATEDIFF(DAY,'20000101',[date])%7,0),7) PERSISTED

But, same error.

Instead of using an implicit convert from a string literal that represents a date time in an unambiguous (to us, but not SQL Server) format, we can use the number of days between the "zero date" (1900-01-01) and that date we know is a Saturday (2000-01-01). If we use an integer here to represent the difference in days, SQL Server can't complain, because there is no way to misinterpret that number. So this works:

-- SELECT DATEDIFF(DAY, 0, '20000101');  -- 36524

  DayOfWeek_Number AS 
    -----------------------------^^^^^  only change


If you're interested in pursuing computed columns for some of these calculations, let me know.

Oh, and one last thing: I don't know why you would ever scrub down this table and re-populate it from scratch. How many of these things are going to change? Are you going to alter your fiscal year constantly? Change how you want to spell March? Set your week to start on Monday one week and Thursday the next? This really should be a build-it-up-once table, and then you make minor tweaks (like updating individual rows with new/changed holiday information).


Here is a single SELECT query that uses the WITH RECURSIVE statement. It does not require loops, procedures etc.

A RECURSIVE SQL common table expression (CTE) is a query that continuously references a previous result until it returns an empty result. It's achieved using a CTE, which in SQL is known as a “WITH” statement.

In the query below, the second date_range with statement has a UNION ALL. The first query in that statement is the first record of the table, and the subsequent records are all from the UNION ALL second statement where it continuously references itself until it reaches the end_date, as expresses as "WHERE next_date <= end_date"

WITH RECURSIVE min_max_dates AS (
    MIN(start_at) start_date,
    MAX(end_at) end_date 
  FROM table_or_hard_code ay
date_range AS (
    start_date date,
    DATE_TRUNC('month', start_date) month_date,
    MONTH(start_date) as month_num,
    YEAR(start_date) as month_year,
    TO_CHAR((start_date),'MMMM') as month_display,
    MONTHNAME (start_date) as month_short_display,
    CONCAT(TO_CHAR((start_date),'MMMM'), ', ', YEAR(start_date)) as month_year_display,
    DATEADD(DAY, 1, start_date) as next_date,
  FROM min_max_dates
    dr.next_date date,
    DATE_TRUNC('month', dr.next_date) month_date,
    MONTH(dr.next_date) as month_num,
    YEAR(dr.next_date) as month_year,
    TO_CHAR((dr.next_date),'MMMM') as month_display,
    MONTHNAME (dr.next_date) as month_short_display,
    CONCAT(TO_CHAR((dr.next_date),'MMMM'), ', ', YEAR(dr.next_date)) as month_year_display,
    DATEADD(DAY, 1, dr.next_date) as next_date,
  FROM date_range dr
  WHERE next_date <= end_date
SELECT date, month_date, month_year, month_num, month_display, month_short_display, month_year_display
FROM date_range

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.