Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Creating a date dimension or calendar table in SQL Server


By:   |   Read Comments (27)   |   Related Tips: 1 | 2 | 3 | More > Dates

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

A calendar table can be immensely useful, particularly for reporting purposes, and for determining things like business days between two dates. I often see people struggling with manually populating a calendar or date dimension table; usually there are lots of loops and iterative code constructs being used. In this tip I will show you how to build and use a calendar table.

Solution

I build calendar tables all the time, for a variety of business applications, and have come up with a few ways to handle things. Sharing them here will hopefully prevent you from re-inventing any wheels when populating your own tables.

One of the biggest objections I hear to calendar tables is that people don't want to create a table. I can't stress enough how cheap a table can be in terms of size and memory usage, especially as storage continues to be larger and faster, compared to using all kinds of functions to determine date-related information on every single query. The table I create below probably has a lot more materialized columns than you would ever need, but it takes a whopping 1.29 MB on disk and in memory (that covers 20 years; 30 years would be 1.86 MB, and 50 years would be 3.08 MB). That will go up as you implement additional indexes, but still represents an extremely negligible impact in most systems.

I also always explicitly set things like DATEFORMAT, DATEFIRST, and LANGUAGE to avoid ambiguity, default to English for month and day names, and assume that quarters for the fiscal year align with the calendar year. You may need to change some of these things depending on your display language, your fiscal year, and other factors.

This is a one-time population, so I'm not worried about the costs of using intermediate storage like temp tables. I like to materialize all of the columns to disk, rather than rely on computed columns, since the table becomes read-only after initial population. So I'm going to do a lot of those calculations during the initial population of the #temp table:

DECLARE @StartDate DATE = '20000101', @NumberOfYears INT = 30;

-- prevent set or regional settings from interfering with 
-- interpretation of dates / literals

SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE US_ENGLISH;

DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);

-- this is just a holding table for intermediate calculations:

CREATE TABLE #dim
(
  [date]       DATE PRIMARY KEY, 
  [day]        AS DATEPART(DAY,      [date]),
  [month]      AS DATEPART(MONTH,    [date]),
  FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),
  [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]),
  FirstOfYear  AS CONVERT(DATE, DATEADD(YEAR,  DATEDIFF(YEAR,  0, [date]), 0)),
  Style112     AS CONVERT(CHAR(8),   [date], 112),
  Style101     AS CONVERT(CHAR(10),  [date], 101)
);

-- use the catalog views to generate as many rows as we need

INSERT #dim([date]) 
SELECT d
FROM
(
  SELECT d = DATEADD(DAY, rn - 1, @StartDate)
  FROM 
  (
    SELECT 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
    -- on my system this would support > 5 million days
    ORDER BY s1.[object_id]
  ) AS x
) AS y;

At this point, #dim looks like this, just showing the first 5 and last 5 dates:

Truncated contents of #dim temporary table

Now these pre-calculated values can help to derive all of the other materialized columns you might want in your calendar table. The following is just a sampling of the things I see most commonly; I am sure that you do not need all of these columns, and that there might be other columns you need. You should just use this as a starting point:

CREATE TABLE dbo.DateDimension
(
  DateKey             INT         NOT NULL PRIMARY KEY,
  [Date]              DATE        NOT NULL,
  [Day]               TINYINT     NOT NULL,
  DaySuffix           CHAR(2)     NOT NULL,
  [Weekday]           TINYINT     NOT NULL,
  WeekDayName         VARCHAR(10) NOT NULL,
  IsWeekend           BIT         NOT NULL,
  IsHoliday           BIT         NOT NULL,
  HolidayText         VARCHAR(64) SPARSE,
  DOWInMonth          TINYINT     NOT NULL,
  [DayOfYear]         SMALLINT    NOT NULL,
  WeekOfMonth         TINYINT     NOT NULL,
  WeekOfYear          TINYINT     NOT NULL,
  ISOWeekOfYear       TINYINT     NOT NULL,
  [Month]             TINYINT     NOT NULL,
  [MonthName]         VARCHAR(10) NOT NULL,
  [Quarter]           TINYINT     NOT NULL,
  QuarterName         VARCHAR(6)  NOT NULL,
  [Year]              INT         NOT NULL,
  MMYYYY              CHAR(6)     NOT NULL,
  MonthYear           CHAR(7)     NOT NULL,
  FirstDayOfMonth     DATE        NOT NULL,
  LastDayOfMonth      DATE        NOT NULL,
  FirstDayOfQuarter   DATE        NOT NULL,
  LastDayOfQuarter    DATE        NOT NULL,
  FirstDayOfYear      DATE        NOT NULL,
  LastDayOfYear       DATE        NOT NULL,
  FirstDayOfNextMonth DATE        NOT NULL,
  FirstDayOfNextYear  DATE        NOT NULL
);
GO

-- create other useful index(es) here

A couple of notes:

  • DateKey is an INT not because that is my preference (I would always store this as a DATE), but because that seems to be the preference of most data warehousing professionals.
  • DOWInMonth is the occurrence of that weekday within the current month - 1st Sunday, 3rd Monday, etc.

Now to populate this table from our #dim object, it is a relatively straightforward INSERT/SELECT; still, you'll see why I pre-calculated some of the values, since many of the expressions are used multiple times:

INSERT dbo.DateDimension WITH (TABLOCKX)
SELECT
  DateKey       = CONVERT(INT, Style112),
  [Date]        = [date],
  [Day]         = CONVERT(TINYINT, [day]),
  DaySuffix     = CONVERT(CHAR(2), CASE WHEN [day] / 10 = 1 THEN 'th' ELSE 
                  CASE RIGHT([day], 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd' 
	              WHEN '3' THEN 'rd' ELSE 'th' END END),
  [Weekday]     = CONVERT(TINYINT, [DayOfWeek]),
  [WeekDayName] = CONVERT(VARCHAR(10), DATENAME(WEEKDAY, [date])),
  [IsWeekend]   = CONVERT(BIT, CASE WHEN [DayOfWeek] IN (1,7) THEN 1 ELSE 0 END),
  [IsHoliday]   = CONVERT(BIT, 0),
  HolidayText   = CONVERT(VARCHAR(64), NULL),
  [DOWInMonth]  = CONVERT(TINYINT, ROW_NUMBER() OVER 
                  (PARTITION BY FirstOfMonth, [DayOfWeek] ORDER BY [date])),
  [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(VARCHAR(10), [MonthName]),
  [Quarter]     = CONVERT(TINYINT, [quarter]),
  QuarterName   = CONVERT(VARCHAR(6), CASE [quarter] WHEN 1 THEN 'First' 
                  WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END), 
  [Year]        = [year],
  MMYYYY        = CONVERT(CHAR(6), LEFT(Style101, 2)    + LEFT(Style112, 4)),
  MonthYear     = CONVERT(CHAR(7), LEFT([MonthName], 3) + LEFT(Style112, 4)),
  FirstDayOfMonth     = FirstOfMonth,
  LastDayOfMonth      = MAX([date]) OVER (PARTITION BY [year], [month]),
  FirstDayOfQuarter   = MIN([date]) OVER (PARTITION BY [year], [quarter]),
  LastDayOfQuarter    = MAX([date]) OVER (PARTITION BY [year], [quarter]),
  FirstDayOfYear      = FirstOfYear,
  LastDayOfYear       = MAX([date]) OVER (PARTITION BY [year]),
  FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),
  FirstDayOfNextYear  = DATEADD(YEAR,  1, FirstOfYear)
FROM #dim
OPTION (MAXDOP 1);

We're not done yet; all of the IsHoliday values are still set to 0. Since I am in the United States, I'm going to deal with statutory holidays here; of course, if you live in another country, you'll need to use different logic here. You'll also need to add your own company's holidays manually, but hopefully if you have things that are deterministic, like bank holidays, Boxing Day, or the third Monday of July is your annual off-site arm-wrestling tournament, you should be able to do most of that without much work by following the same sort of pattern I use below. We can update most of the stat holidays with a single pass and rather simple criteria:

;WITH x AS 
(
  SELECT DateKey, [Date], IsHoliday, HolidayText, FirstDayOfYear,
    DOWInMonth, [MonthName], [WeekDayName], [Day],
    LastDOWInMonth = ROW_NUMBER() OVER 
    (
      PARTITION BY FirstDayOfMonth, [Weekday] 
      ORDER BY [Date] DESC
    )
  FROM dbo.DateDimension
)
UPDATE x SET IsHoliday = 1, HolidayText = CASE
  WHEN ([Date] = FirstDayOfYear) 
    THEN 'New Year''s Day'
  WHEN ([DOWInMonth] = 3 AND [MonthName] = 'January' AND [WeekDayName] = 'Monday')
    THEN 'Martin Luther King Day'    -- (3rd Monday in January)
  WHEN ([DOWInMonth] = 3 AND [MonthName] = 'February' AND [WeekDayName] = 'Monday')
    THEN 'President''s Day'          -- (3rd Monday in February)
  WHEN ([LastDOWInMonth] = 1 AND [MonthName] = 'May' AND [WeekDayName] = 'Monday')
    THEN 'Memorial Day'              -- (last Monday in May)
  WHEN ([MonthName] = 'July' AND [Day] = 4)
    THEN 'Independence Day'          -- (July 4th)
  WHEN ([DOWInMonth] = 1 AND [MonthName] = 'September' AND [WeekDayName] = 'Monday')
    THEN 'Labour Day'                -- (first Monday in September)
  WHEN ([DOWInMonth] = 2 AND [MonthName] = 'October' AND [WeekDayName] = 'Monday')
    THEN 'Columbus Day'              -- Columbus Day (second Monday in October)
  WHEN ([MonthName] = 'November' AND [Day] = 11)
    THEN 'Veterans'' Day'            -- Veterans' Day (November 11th)
  WHEN ([DOWInMonth] = 4 AND [MonthName] = 'November' AND [WeekDayName] = 'Thursday')
    THEN 'Thanksgiving Day'          -- Thanksgiving Day (fourth Thursday in November)
  WHEN ([MonthName] = 'December' AND [Day] = 25)
    THEN 'Christmas Day'
  END
WHERE 
  ([Date] = FirstDayOfYear)
  OR ([DOWInMonth] = 3     AND [MonthName] = 'January'   AND [WeekDayName] = 'Monday')
  OR ([DOWInMonth] = 3     AND [MonthName] = 'February'  AND [WeekDayName] = 'Monday')
  OR ([LastDOWInMonth] = 1 AND [MonthName] = 'May'       AND [WeekDayName] = 'Monday')
  OR ([MonthName] = 'July' AND [Day] = 4)
  OR ([DOWInMonth] = 1     AND [MonthName] = 'September' AND [WeekDayName] = 'Monday')
  OR ([DOWInMonth] = 2     AND [MonthName] = 'October'   AND [WeekDayName] = 'Monday')
  OR ([MonthName] = 'November' AND [Day] = 11)
  OR ([DOWInMonth] = 4     AND [MonthName] = 'November' AND [WeekDayName] = 'Thursday')
  OR ([MonthName] = 'December' AND [Day] = 25);

(You may have to perform some manual modifications to some of those, in the case where they fall on a weekend - usually the following Monday is marked as the holiday instead.)

Black Friday is a little trickier, because it's the Friday after the fourth Thursday in November, and so it might be the fourth Friday, but several times a century it is actually the fifth Friday:

UPDATE d SET IsHoliday = 1, HolidayText = 'Black Friday'
FROM dbo.DateDimension AS d
INNER JOIN
(
  SELECT DateKey, [Year], [DayOfYear]
  FROM dbo.DateDimension 
  WHERE HolidayText = 'Thanksgiving Day'
) AS src 
ON d.[Year] = src.[Year] 
AND d.[DayOfYear] = src.[DayOfYear] + 1;

And then there's Easter. This has always been a complicated problem; the rules for calculating the exact date are so convoluted, I suspect most people can only mark those dates where they have physical calendars they can look at to confirm. If your company doesn't recognize Easter, you can skip ahead; if it does, you can use the following function, which will return the Easter holiday dates for any given year:

CREATE FUNCTION dbo.GetEasterHolidays(@year INT) 
RETURNS TABLE
WITH SCHEMABINDING
AS 
RETURN 
(
  WITH x AS 
  (
    SELECT [Date] = CONVERT(DATE, RTRIM(@year) + '0' + RTRIM([Month]) 
        + RIGHT('0' + RTRIM([Day]),2))
      FROM (SELECT [Month], [Day] = DaysToSunday + 28 - (31 * ([Month] / 4))
      FROM (SELECT [Month] = 3 + (DaysToSunday + 40) / 44, DaysToSunday
      FROM (SELECT DaysToSunday = paschal - ((@year + @year / 4 + paschal - 13) % 7)
      FROM (SELECT paschal = epact - (epact / 28)
      FROM (SELECT epact = (24 + 19 * (@year % 19)) % 30) 
        AS epact) AS paschal) AS dts) AS m) AS d
  )
  SELECT [Date], HolidayName = 'Easter Sunday' FROM x
    UNION ALL SELECT DATEADD(DAY,-2,[Date]), 'Good Friday'   FROM x
    UNION ALL SELECT DATEADD(DAY, 1,[Date]), 'Easter Monday' FROM x
);

(You can adjust the function easily, depending on whether they recognize just Easter Sunday or also Good Friday and/or Easter Monday. There is also another tip here that will show you how to determine the date for Mardi Gras, given the date for Easter.)

Now, to use that function to mark the Easter holidays in the calendar table:

;WITH x AS 
(
  SELECT d.[Date], d.IsHoliday, d.HolidayText, h.HolidayName
    FROM dbo.DateDimension AS d
    CROSS APPLY dbo.GetEasterHolidays(d.[Year]) AS h
    WHERE d.[Date] = h.[Date]
)
UPDATE x SET IsHoliday = 1, HolidayText = HolidayName;

And now you have a functional calendar table you can use for all of your reporting or business needs.

Summary

Creating a dimension or calendar table for business dates and fiscal periods might seem intimidating at first, but once you have a solid methodology in line, it can be very worthwhile. There are many ways to do this; some will subscribe to the idea that many of these date-related facts can be derived at query time, or at least be non-persisted computed columns. You will have to decide if the values are calculated often enough to justify the additional space on disk and in the buffer pool.

If you are using Enterprise Edition on SQL Server 2014 or above, you could consider using In-Memory OLTP, and possibly even a non-durable table that you rebuild using a startup procedure. Or on any version or edition, you could put the calendar table into its own filegroup (or database), and mark it as read-only after initial population (this won't force the table to stay in memory all the time, but it will reduce other types of contention).

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, December 05, 2017 - 2:43:07 PM - Devang Mistry Back To Top

 Hello,

 

Thanks for the great post, saves a lot of time. In my case i set the Start date as 20101001 as fiscal year starts from 1st october. What would be the change if i want to see 1 in the month column instead of 10. I need to make sure that october is the first month of the fiscal year not 10th. 

Any help will be greatly appreciated. Thanks.


Monday, November 27, 2017 - 5:05:45 PM - Aaron Bertrand Back To Top

Kirk, 

(1) if you think you will need to support more than one holiday on the same day, and you didn't want to put "Father's Day and Pokemon Day" in the HolidayText column, then sure, you could use another table for that. It isn't a requirement that I've come across in my experience to date, and there are probably a lot of other less common requirements my solution doesn't cover, either.

(2) LastDOWInMonth is certainly defined at runtime in the CTE (just search this page for that term). It's not meant to be in the main dimension table, but again, if this is a requirement for you, obviously it's easy to add it to the table.


Monday, November 27, 2017 - 3:52:45 PM - Kirk Back To Top

A question and a slight hicup in the code that I see.

First, my question is what would be the downside of putting holidays in a seperate table (outside a join). As a developer I like the idea that the difference between instances would be isolated to a seperate table. Also, it could allow multiple holidays on the the same date if desired.

 My observation is that you use LastDOWInMonth to define Memorial Day. Which is correct, but the column isn't in your table. I easily added it setting the default to 0 then updating with the following:

UPDATE DateDimension

SET LastDOWInMonth = 1

WHERE Date IN 

(

    SELECT dd.Date

    FROM DateDimension dd

    WHERE DATEPART(month, DATEADD(day, 7, dd.Date)) <> DATEPART(month, dd.Date)

)


Friday, November 10, 2017 - 2:45:32 AM - Anne-Maarit Back To Top

 Thank you so much, this saved me days of time. It worked amazingly.

Anne-Maarit

 


Tuesday, November 07, 2017 - 5:20:05 PM - alan Back To Top

 This was awesome, a real time saver for me and the processor. 

 

Kudos!

 


Friday, November 03, 2017 - 5:11:07 AM - Bajke Back To Top

Please use this link https://www.timeanddate.com/date/weeknumber.html#!week=53&year=2014

Then Expand for more options and set the First day of week to Sunday.

Observe which days are in the w53 2014 and wich days belong to w1 2015

Do you notice the difference I mentioned when compared to results from your code?


Thursday, November 02, 2017 - 4:44:22 PM - Greg Robidoux Back To Top

The first 4 days for that week 1,2,3,4 were the last 4 days of 2014.  It is based on the calendar.  So since Jan 1, 2015 started on a Thursday and the weeks begin on a Sunday there were only 3 days in the first calendar week in 2015.


Thursday, November 02, 2017 - 4:25:53 PM - Bajke Back To Top

Exactly! Every week must contain exactley 7 days. :)

So, what happent to 4 more days in 1st week of 2015?


Thursday, November 02, 2017 - 3:50:14 PM - Greg Robidoux Back To Top

Hi Bajke,

I just ran this code and it only shows three days for the first week of 2015. 

The days of the week are the 1st = 5 (Thursday), the 2nd = 6 (Friday) and 3rd = 7 (Saturday).

Let us know if you see something different.

-Greg


Thursday, November 02, 2017 - 1:44:24 PM - Bajke Back To Top

I thing that this solution has a flaw. For example, the 1st week in 2015 does not have 7 days.

Best Regards,

 


Saturday, September 09, 2017 - 11:10:16 PM - stephen E Okala Back To Top

 I am buliding one with multiple calendars - four fiscal-year calendars. How do I handle that. Please help.

Stephen

 


Wednesday, March 22, 2017 - 2:01:53 PM - Joe Celko Back To Top

 Easter is worse than your implying here. Many decades ago, I wanted to put future Easter dates into a calendar, and I gave the job to two junior programmers. I assumed they would look up the algorithm, but one of them said, "this is silly! I'll just go down and ask my priest for a calendar and I'll know it is right!"; I said we really should check this out a couple of sources just to be sure, etc. so another junior programmer volunteered to verify the other guys work (yes, we used to do code verification and walk-through, back in those days)

 

Unfortunately, one of them was Greek Orthodox and the other was Russian Orthodox.

 


Wednesday, March 22, 2017 - 6:35:50 AM - Maz Back To Top

Hi

Using you tip/code Aaron as the base I've created a DateDimension table that includes bank holidays for England Wales.

https://github.com/SQL-RedUnited/SQL-Snippets/blob/master/CREATE_DateDimension_table_EnglandWales.sql

I hope someone finds this of use.

 

 

 


Friday, March 17, 2017 - 12:22:19 PM - KeviM Back To Top

Great article.  Thanks for sharing. 

How would I add the dates for 'FirstOfWeek and 'Lastofweek'?  (Where Monday is the first day of the week)

 


Tuesday, February 14, 2017 - 12:27:36 PM - Davide Moraschi Back To Top

It is an extemely useful script.
Kudos

Davide.


Monday, February 13, 2017 - 9:30:10 AM - Joe Back To Top

 This is awesome. Will save LOTS of time programmatically too (transactions processing) as well as doing data analysis.

 


Monday, January 23, 2017 - 2:33:54 PM - Robert Back To Top

Thank you for this write up. I am kind of surprised this table doesn't have a bit more of the oddities. I have included them below.

 

 

[IsLeapYear]  = CONVERT(BIT, CASE datepart(mm, dateadd(dd, 1, cast((cast([year] as varchar(4)) + '0228') as datetime))) when 2 then 1 else 0 END),

 

[Has53Weeks]  = CONVERT(BIT, CASE WHEN (5 * [YEAR] + 12 - 4 * (FLOOR([YEAR]/100) - FLOOR([YEAR]/400)) + FLOOR(([YEAR] - 100)/400) - FLOOR(([YEAR] - 102)/400)  + FLOOR(([YEAR] - 200)/400) - FLOOR(([YEAR] - 199)/400)) % 28 < 5 THEN 1 ELSE 0 END),

 

ISOYear = CONVERT(INT, (SELECT [dbo].[ISOyear]([date]))),

 

--where ISOyear is

CREATE FUNCTION [dbo].[ISOyear](@date DATETIME)

returns SMALLINT

AS

BEGIN

     DECLARE @isoyear SMALLINT = CASE

         WHEN Datepart(isowk, @date)=1

             AND Month(@date)=12 THEN Year(@date)+1

         WHEN Datepart(isowk, @date)=53

             AND Month(@date)=1 THEN Year(@date)-1

         WHEN Datepart(isowk, @date)=52

             AND Month(@date)=1 THEN Year(@date)-1             

         ELSE Year(@date)

        END;

     RETURN @isoyear;

END;

 

 

 

 

 


Saturday, October 22, 2016 - 2:17:27 AM - Brian Back To Top

Fantastic write-up - extremely simple to follow. I'm relatively new to implementing dimension tables, so it's much appreciated.

Can you elaborate a bit on your differences in choosing the date as the pk as opposed using an int? Date would intuitively seem an easier type to manage across tables, to me. What is the advantage in using an int?


Friday, October 14, 2016 - 12:18:19 PM - Erin G Back To Top

 Thank you.  I think this will be quite helpful.  I am a relatively new DBA, < 3 years.  I have been asked to develope a database that can be used to search and then schedule resources.  Kind of like booking a hotel room.  I have saved the page so that I can come back to it when I figure out the rest of the project. 

 


Tuesday, September 13, 2016 - 6:22:03 AM - Guss Davey Back To Top

 

Some helper TSQL (thanks for the rest)

--Whenever a public holiday falls on a Sunday, the Monday following it will be a public holiday

UPDATE nextMonday SET nextMonday.IsHoliday = 1, 

HolidayText = CASE WHEN ISNULL(nextMonday.HolidayText,'')='' THEN 'Monday after ' + holidayOnSunday.HolidayText 

ELSE nextMonday.HolidayText END

FROM dbo.DateDimension nextMonday INNER JOIN dbo.DateDimension holidayOnSunday

ON nextMonday.[Date] = DATEADD(day,1,holidayOnSunday.[Date])

WHERE holidayOnSunday.IsHoliday = 1  AND holidayOnSunday.WeekdayName = 'Sunday'

;

 

--In Western Christianity, using the Gregorian calendar, 

--Easter always falls on a Sunday between 22 March and 25 April inclusive

UPDATE dbo.DateDimension SET IsHoliday = 1, HolidayText = 'Easter Sunday' 

WHERE WeekdayName = 'Sunday' AND MonthName='April' AND [Day] BETWEEN 20 AND 26

;

 

--The Monday following Easter SundayFamily Day

UPDATE nextMonday SET nextMonday.IsHoliday = 1, nextMonday.HolidayText = 'Family Day'

FROM dbo.DateDimension nextMonday INNER JOIN dbo.DateDimension easter

ON nextMonday.[Date] = DATEADD(day,1,easter.[Date])

WHERE easter.HolidayText = 'Easter Sunday'

;

 

--The Friday before Easter Sunday = Good Friday

UPDATE previousFriday SET previousFriday.IsHoliday = 1, previousFriday.HolidayText = 'Good Friday'

FROM dbo.DateDimension previousFriday INNER JOIN dbo.DateDimension easter

ON previousFriday.[Date] = DATEADD(day,-2,easter.[Date])

WHERE easter.HolidayText = 'Easter Sunday'

;


Wednesday, September 07, 2016 - 10:03:43 AM - Erik Back To Top

I'm surprised it doesn't throw an error when you get the language to US_ENGLISH and then pass in 'Labour Day'.


Friday, September 02, 2016 - 3:05:42 AM - Gavin Kelman Back To Top

 Hi i need a normal calendar as you have prepared but i also need a fiscal calendar starting 01 October. Each fiscal period ends on the last Friday of each month. I would appreciate a complete script if possible. Thanks in advance

 

Gavin Kelman

 


Tuesday, August 02, 2016 - 3:59:36 PM - vishnu Back To Top

 Hi,

could you show me how to modified the code when the holiday falls on a sunday and the holiday is observed on monday.  Very Much Appreciated. Thanks.  

 


Tuesday, March 29, 2016 - 12:33:14 AM - Gustavo Schneider Back To Top

Very helpful. Thanks for sharing, my original search was to understand half of things covered on your post, but after seeing it all it lead to answers things i haven't thought yet.  

 


Tuesday, January 12, 2016 - 5:30:53 PM - Lee Everest Back To Top

Nice, Aaron. Thanks! 

 


Tuesday, October 20, 2015 - 7:30:03 PM - Henry Stinson Back To Top

I like the article and find the parts about holidays, etc especially valuable, and especially the way you generated the date column.

I thought of using a CTE using the fact that adding 1 to a DateTime data type would add a day, which may not be as fast as your method since it causes a RBAR conversion from DATETIME to DATE data type.

 

DECLARE @Today DATETIME,

        @EndDate DATE;

SET @Today = CONVERT(DATE, GETDATE());

SET @EndDate = '01/01/2016';

 

SELECT 'END DATE = ' + CONVERT(VARCHAR, @EndDate);

SELECT 'START DATE = ' + CONVERT(VARCHAR, @Today);

 

WITH cte AS

( SELECT @Today AS d

  UNION ALL

  SELECT d + 1 AS d

  FROM cte

  WHERE d+1 < @EndDate

)

INSERT INTO #dim([Date])

SELECT d

FROM cte;

 

SELECT 100 *

FROM #dim;

 

In the calculations for first of month and first of year, I didn't like the multiple nested function calls and was thinking of other ways to do that, but not sure I could come up with anything better without spending too much time to do it.  I thought of pulliing out datapart month and year and concatenating those with day '01', but I decided not to even experiement, because I doubt my method would be much faster.

Then I thought of creating a separate table with just first-of-month dates and joining with that to populate the FirstOfMonth dates and FirstOfYear dates in the temp table.  I'm not sure exactly what that join would look like without experimenting, but I'll just throw the thought out there.

But in the end, trying to optimize loading of the table may not be needed, especially given the power of modern hardware and SQL Server.


Tuesday, October 20, 2015 - 3:36:32 PM - Renan Back To Top

Nice tip.

Very easy to use and customize.

Thanks.

Renan


Learn more about SQL Server tools