Creating a date dimension or calendar table in SQL Server

By:   |   Updated: 2020-04-06   |   Comments (83)   |   Related: 1 | 2 | 3 | 4 | 5 | More > Dates


   Free MSSQLTips whitepaper - "Understanding Windows Server Cluster Quorum Options"

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 using a set-based solution that is powerful and easily customizable.

Solution

I build calendar tables all the time, for a variety of business applications, and have come up with a few ways to handle certain details. 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 underlying storage continues to be larger and faster, compared to using all kinds of functions to determine date-related information in every single query. Twenty or thirty years of dates stored in a table takes a few MBs at most, even less with compression, and if you use them often enough, they'll always be in memory.

I also always explicitly set things like DATEFORMAT, DATEFIRST, and LANGUAGE to avoid ambiguity, default to U.S. English for week starts and 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 specifics depending on your display language, your fiscal year, and other factors.

This is a one-time population, so I'm not worried about speed, even though this specific CTE approach is no slouch. 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 series of CTEs. To start, I'll show the output of each CTE one at a time.

You can change some of these details to experiment on your own. In this example, I'm going to populate the date dimension table with data spanning 30 years, starting from 2010-01-01.

First, we have a recursive CTE that returns a sequence representing the number of days between our start date (2010-01-01) and 30 years later less a day (2039-12-31):

-- prevent set or regional settings from interfering with 
 -- interpretation of dates / literals
 SET DATEFIRST 7, -- 1 = Monday, 7 = Sunday
 DATEFORMAT mdy, 
 LANGUAGE US_ENGLISH;
 -- assume the above is here in all subsequent code blocks.
 
 DECLARE @StartDate date ='20100101';
 
 DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));
 
 ;WITH seq(n) AS 
 (
 SELECT 0 UNION ALL SELECT n + 1 FROM seq
 WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
 )
 SELECT n FROM seq
 ORDER BY n 
 OPTION (MAXRECURSION 0);

This returns the following list of numbers:

Next, we can add a second CTE that translates those numbers into all the dates in our range:

DECLARE @StartDate date ='20100101';
 
 DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));
 
 ;WITH seq(n) AS 
 (
 SELECT 0 UNION ALL SELECT n + 1 FROM seq
 WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
 ),
 d(d) AS 
 (
 SELECT DATEADD(DAY, n, @StartDate) FROM seq
 )
 SELECT d FROM d
 ORDER BY d
 OPTION (MAXRECURSION 0);

Which returns the following range of dates:

Now, we can start extending those dates with information commonly vital to calendar tables / date dimensions. Many are bits of information you can extract from the date, but it's more convenient to have them readily available in a view or table than it is to have every query calculate them inline. I'm working a little backward here, but I'm going to create an intermediate CTE to extract exactly once some computations I'll later have to make multiple times. This query:

DECLARE @StartDate date ='20100101';
 
 DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));
 
 ;WITH seq(n) AS 
 (
 SELECT 0 UNION ALL SELECT n + 1 FROM seq
 WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
 ),
 d(d) AS 
 (
 SELECT DATEADD(DAY, n, @StartDate) FROM seq
 ),
 src AS
 (
 SELECT
 TheDate = CONVERT(date, d),
 TheDay = DATEPART(DAY, d),
 TheDayName = DATENAME(WEEKDAY, d),
 TheWeek = DATEPART(WEEK, d),
 TheISOWeek = DATEPART(ISO_WEEK, d),
 TheDayOfWeek = DATEPART(WEEKDAY, d),
 TheMonth = DATEPART(MONTH, d),
 TheMonthName = DATENAME(MONTH, d),
 TheQuarter = DATEPART(Quarter, d),
 TheYear = DATEPART(YEAR, d),
 TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
 TheLastOfYear = DATEFROMPARTS(YEAR(d), 12, 31),
 TheDayOfYear = DATEPART(DAYOFYEAR, d)
 FROM d
 )
 SELECT * FROM src
 ORDER BY TheDate
 OPTION (MAXRECURSION 0);

Yields this data:

If you wanted your fiscal year aligned differently, you could change the year and quarter calculations, or add additional columns. Let's say your fiscal year starts October 1st, then depending on whether that's 9 months late or 3 months early, you could just substitute d for a DATEADD expression:

;WITH q AS (SELECT d FROM 
 (
 VALUES('20200101'),
 ('20200401'),
 ('20200701'),
 ('20201001')
 ) AS d(d))
 SELECT
 d, 
 StandardQuarter = DATEPART(QUARTER, d),
 LateFiscalQuarter = DATEPART(QUARTER, DATEADD(MONTH, -9, d)),
 LateFiscalQuarterYear = YEAR(DATEADD(MONTH, -9, d)),
 EarlyFiscalQuarter = DATEPART(QUARTER, DATEADD(MONTH, 3, d)),
 EarlyFiscalQuarterYear = YEAR(DATEADD(MONTH, 3, d))
 FROM q;

Whatever my source data is, I can build on those parts and get much more detail about each date:

DECLARE @StartDate date ='20100101';
 
 DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));
 
 ;WITH seq(n) AS 
 (
 SELECT 0 UNION ALL SELECT n + 1 FROM seq
 WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
 ),
 d(d) AS 
 (
 SELECT DATEADD(DAY, n, @StartDate) FROM seq
 ),
 src AS
 (
 SELECT
 TheDate = CONVERT(date, d),
 TheDay = DATEPART(DAY, d),
 TheDayName = DATENAME(WEEKDAY, d),
 TheWeek = DATEPART(WEEK, d),
 TheISOWeek = DATEPART(ISO_WEEK, d),
 TheDayOfWeek = DATEPART(WEEKDAY, d),
 TheMonth = DATEPART(MONTH, d),
 TheMonthName = DATENAME(MONTH, d),
 TheQuarter = DATEPART(Quarter, d),
 TheYear = DATEPART(YEAR, d),
 TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
 TheLastOfYear = DATEFROMPARTS(YEAR(d), 12, 31),
 TheDayOfYear = DATEPART(DAYOFYEAR, d)
 FROM d
 ),
 dim AS
 (
 SELECT
 TheDate, 
 TheDay,
 TheDaySuffix = CONVERT(char(2), CASE WHEN TheDay / 10 = 1 THEN 'th' ELSE 
 CASE RIGHT(TheDay, 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd' 
 WHEN '3' THEN 'rd' ELSE 'th' END END),
 TheDayName,
 TheDayOfWeek,
 TheDayOfWeekInMonth = CONVERT(tinyint, ROW_NUMBER() OVER 
 (PARTITION BY TheFirstOfMonth, TheDayOfWeek ORDER BY TheDate)),
 TheDayOfYear,
 IsWeekend = CASE WHEN TheDayOfWeek IN (CASE @@DATEFIRST WHEN 1 THEN 6 WHEN 7 THEN 1 END,7) 
 THEN 1 ELSE 0 END,
 TheWeek,
 TheISOweek,
 TheFirstOfWeek = DATEADD(DAY, 1 - TheDayOfWeek, TheDate),
 TheLastOfWeek = DATEADD(DAY, 6, DATEADD(DAY, 1 - TheDayOfWeek, TheDate)),
 TheWeekOfMonth = CONVERT(tinyint, DENSE_RANK() OVER 
 (PARTITION BY TheYear, TheMonth ORDER BY TheWeek)),
 TheMonth,
 TheMonthName,
 TheFirstOfMonth,
 TheLastOfMonth = MAX(TheDate) OVER (PARTITION BY TheYear, TheMonth),
 TheFirstOfNextMonth = DATEADD(MONTH, 1, TheFirstOfMonth),
 TheLastOfNextMonth = DATEADD(DAY, -1, DATEADD(MONTH, 2, TheFirstOfMonth)),
 TheQuarter,
 TheFirstOfQuarter = MIN(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
 TheLastOfQuarter = MAX(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
 TheYear,
 TheISOYear = TheYear - CASE WHEN TheMonth = 1 AND TheISOWeek > 51 THEN 1 
 WHEN TheMonth = 12 AND TheISOWeek = 1 THEN -1 ELSE 0 END, 
 TheFirstOfYear = DATEFROMPARTS(TheYear, 1, 1),
 TheLastOfYear,
 IsLeapYear = CONVERT(bit, CASE WHEN (TheYear % 400 = 0) 
 OR (TheYear % 4 = 0 AND TheYear % 100 0) 
 THEN 1 ELSE 0 END),
 Has53Weeks = CASE WHEN DATEPART(WEEK, TheLastOfYear) = 53 THEN 1 ELSE 0 END,
 Has53ISOWeeks = CASE WHEN DATEPART(ISO_WEEK, TheLastOfYear) = 53 THEN 1 ELSE 0 END,
 MMYYYY = CONVERT(char(2), CONVERT(char(8), TheDate, 101))
 + CONVERT(char(4), TheYear),
 Style101 = CONVERT(char(10), TheDate, 101),
 Style103 = CONVERT(char(10), TheDate, 103),
 Style112 = CONVERT(char(8), TheDate, 112),
 Style120 = CONVERT(char(10), TheDate, 120)
 FROM src
 )
 SELECT * FROM dim
 ORDER BY TheDate
 OPTION (MAXRECURSION 0);

This adds supplemental information about any given date, such as the first of period / last of period the date falls within, whether it is a leap year, a few popular string formats, and some specific ISO 8601 specifics (I'll talk more about those in another tip). You may only want some of these columns, and you may want others, too. When you're happy with the output, you can change this line:

SELECT * FROM dim

To this:

SELECT * INTO dbo.DateDimension FROM dim

Then you can add a clustered primary key (and any other indexes you want to have handy):

CREATE UNIQUE CLUSTERED INDEX PK_DateDimension ON dbo.DateDimension(TheDate);

To give an idea of how much space this table really takes, even with all those columns that you probably don't need, the max is about 2MB with a regular clustered index defined on the TheDate column, all the way down to 500KB for a clustered columnstore index compressed with COLUMNSTORE_ARCHIVE (not necessarily something you should do, depending on the workload that will work against this table, but since it is effectively read only, the DML overhead isn't really a consideration):

Next, we need to talk about holidays, one of the primary seasons you need to use a calendar table instead of relying on built-in date/time functions. In the original version of this tip, I added an IsHoliday column, but as a comment rightly pointed out, this set is probably best held in a separate table:

CREATE TABLE dbo.HolidayDimension
 (
 TheDate date NOT NULL,
 HolidayText nvarchar(255) NOT NULL,
 CONSTRAINT FK_DateDimension FOREIGN KEY(TheDate) REFERENCES dbo.DateDimension(TheDate)
 );
 
 CREATE CLUSTERED INDEX CIX_HolidayDimension ON dbo.HolidayDimension(TheDate);
 GO

This allows you to have more than one holiday for any given date, and in fact allows for multiple entire calendars each with their own set of holidays (imagine an additional column specifying the CalendarID).

Populating the holiday dimension table can be complex. 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. 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. You may also have to add some logic if your company observes weekend holidays on the previous or following weekday, which gets even more complex if those happen to collide with other company- or industry-specific non-business days. We can add most of the traditional holidays with a single pass and rather simple criteria:

 ;WITH x AS 
 (
 SELECT
 TheDate,
 TheFirstOfYear,
 TheDayOfWeekInMonth, 
 TheMonth, 
 TheDayName, 
 TheDay,
 TheLastDayOfWeekInMonth = ROW_NUMBER() OVER 
 (
 PARTITION BY TheFirstOfMonth, TheDayOfWeek
 ORDER BY TheDate DESC
 )
 FROM dbo.DateDimension
 ),
 s AS
 (
 SELECT TheDate, HolidayText = CASE
 WHEN (TheDate = TheFirstOfYear) 
 THEN 'New Year''s Day'
 WHEN (TheDayOfWeekInMonth = 3 AND TheMonth = 1 AND TheDayName ='Monday')
 THEN 'Martin Luther King Day' -- (3rd Monday in January)
 WHEN (TheDayOfWeekInMonth = 3 AND TheMonth = 2 AND TheDayName ='Monday')
 THEN 'President''s Day' -- (3rd Monday in February)
 WHEN (TheLastDayOfWeekInMonth = 1 AND TheMonth = 5 AND TheDayName ='Monday')
 THEN 'Memorial Day' -- (last Monday in May)
 WHEN (TheMonth = 7 AND TheDay = 4)
 THEN 'Independence Day' -- (July 4th)
 WHEN (TheDayOfWeekInMonth = 1 AND TheMonth = 9 AND TheDayName ='Monday')
 THEN 'Labour Day' -- (first Monday in September)
 WHEN (TheDayOfWeekInMonth = 2 AND TheMonth = 10 AND TheDayName ='Monday')
 THEN 'Columbus Day' -- Columbus Day (second Monday in October)
 WHEN (TheMonth = 11 AND TheDay = 11)
 THEN 'Veterans'' Day' -- (November 11th)
 WHEN (TheDayOfWeekInMonth = 4 AND TheMonth = 11 AND TheDayName ='Thursday')
 THEN 'Thanksgiving Day' -- (Thanksgiving Day ()fourth Thursday in November)
 WHEN (TheMonth = 12 AND TheDay = 25)
 THEN 'Christmas Day'
 END
 FROM x
 WHERE 
 (TheDate = TheFirstOfYear)
 OR (TheDayOfWeekInMonth = 3 AND TheMonth = 1 AND TheDayName ='Monday')
 OR (TheDayOfWeekInMonth = 3 AND TheMonth = 2 AND TheDayName ='Monday')
 OR (TheLastDayOfWeekInMonth = 1 AND TheMonth = 5 AND TheDayName ='Monday')
 OR (TheMonth = 7 AND TheDay = 4)
 OR (TheDayOfWeekInMonth = 1 AND TheMonth = 9 AND TheDayName ='Monday')
 OR (TheDayOfWeekInMonth = 2 AND TheMonth = 10 AND TheDayName ='Monday')
 OR (TheMonth = 11 AND TheDay = 11)
 OR (TheDayOfWeekInMonth = 4 AND TheMonth = 11 AND TheDayName ='Thursday')
 OR (TheMonth = 12 AND TheDay = 25)
 )
 INSERT dbo.HolidayDimension(TheDate, HolidayText)
 SELECT TheDate, HolidayText FROM s 
 UNION ALL 
 SELECT DATEADD(DAY, 1, TheDate), 'Black Friday'
 FROM s WHERE HolidayText ='Thanksgiving Day'
 ORDER BY TheDate;

Black Friday is a little trickier, because it's the Friday after the fourth Thursday in November. Usually that makes it the fourth Friday, but several times a century it is actually the fifth Friday, so the UNION ALL above just grabs the day after each Thanksgiving Day.

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(@TheYear INT) 
 RETURNS TABLE
 WITH SCHEMABINDING
 AS 
 RETURN 
 (
 WITH x AS 
 (
 SELECT TheDate = DATEFROMPARTS(@TheYear, [Month], [Day])
 FROM (SELECT [Month], [Day] = DaysToSunday + 28 - (31 * ([Month] / 4))
 FROM (SELECT [Month] = 3 + (DaysToSunday + 40) / 44, DaysToSunday
 FROM (SELECT DaysToSunday = paschal - ((@TheYear + (@TheYear / 4) + paschal - 13) % 7)
 FROM (SELECT paschal = epact - (epact / 28)
 FROM (SELECT epact = (24 + 19 * (@TheYear % 19)) % 30) 
 AS epact) AS paschal) AS dts) AS m) AS d
 )
 SELECT TheDate, HolidayText ='Easter Sunday' FROM x
 UNION ALL SELECT DATEADD(DAY, -2, TheDate), 'Good Friday' FROM x
 UNION ALL SELECT DATEADD(DAY, 1, TheDate), 'Easter Monday' FROM x
 );
 GO

(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 add the Easter holidays to the HolidayDimension table:

INSERT dbo.HolidayDimension(TheDate, HolidayText)
 SELECT d.TheDate, h.HolidayText
 FROM dbo.DateDimension AS d
 CROSS APPLY dbo.GetEasterHolidays(d.TheYear) AS h
 WHERE d.TheDate = h.TheDate;

Finally, you can create a view that bridges these two tables (or multiple views):

CREATE VIEW dbo.TheCalendar
 AS 
 SELECT
 d.TheDate,
 d.TheDay,
 d.TheDaySuffix,
 d.TheDayName,
 d.TheDayOfWeek,
 d.TheDayOfWeekInMonth,
 d.TheDayOfYear,
 d.IsWeekend,
 d.TheWeek,
 d.TheISOweek,
 d.TheFirstOfWeek,
 d.TheLastOfWeek,
 d.TheWeekOfMonth,
 d.TheMonth,
 d.TheMonthName,
 d.TheFirstOfMonth,
 d.TheLastOfMonth,
 d.TheFirstOfNextMonth,
 d.TheLastOfNextMonth,
 d.TheQuarter,
 d.TheFirstOfQuarter,
 d.TheLastOfQuarter,
 d.TheYear,
 d.TheISOYear,
 d.TheFirstOfYear,
 d.TheLastOfYear,
 d.IsLeapYear,
 d.Has53Weeks,
 d.Has53ISOWeeks,
 d.MMYYYY,
 d.Style101,
 d.Style103,
 d.Style112,
 d.Style120,
 IsHoliday = CASE WHEN h.TheDate IS NOT NULL THEN 1 ELSE 0 END,
 h.HolidayText
 FROM dbo.DateDimension AS d
 LEFT OUTER JOIN dbo.HolidayDimension AS h
 ON d.TheDate = h.TheDate;

And now you have a functional calendar view 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



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights








About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

View all my tips


Article Last Updated: 2020-04-06

Comments For This Article




Thursday, May 12, 2022 - 1:38:58 AM - Rajni KumarBack To Top(90081)
Thank you for this. I am beginner and was haunted by the code before.

Wednesday, April 27, 2022 - 12:35:09 AM - WalterBack To Top(90040)
I'm a beginner, and I must admit this is scary. Thanks for all the complex queries

Thursday, April 21, 2022 - 1:38:49 PM - AndrewBack To Top(90024)
Love it! Where is your "tip jar" or "buy me a coffee" button?

Wednesday, April 13, 2022 - 7:19:27 PM - Todd TaylorBack To Top(90000)
Brilliant... thank you!

Friday, March 25, 2022 - 12:52:54 PM - Aaron BertrandBack To Top(89931)
Hi Donna,

DATEFIRST <> TheDayOfWeek. Try this:

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a98a704b723cb638231db6c35114f5da

It's confusing, but in order to make Sunday the first day of the week, you actually say `SET DATEFIRST 7`. So the comment next to the SET DATEFIRST command reflects what argument you need to pass in order to get the desired weekday to be day of the week 1, not what day of the week those weekdays show. In my calendar table, I want Sunday to be 1 and Monday to be 2 regardless of the DATEFIRST setting I have when populating it or any user might have when querying it (which is why I store it instead of rely on computations later).

The following document explains it probably better than I could.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-datefirst-transact-sql?view=sql-server-ver15

Friday, March 25, 2022 - 11:45:33 AM - DonnaBack To Top(89927)
Hi Aaron,

I noticed in the beginning of the samples you put "SET DATEFIRST 7, -- 1 = Monday, 7 = Sunday", but in the date table field TheDayOfWeek, Sunday is 1 and Monday is 2. Something changed between the statement and the table output?

Tuesday, February 15, 2022 - 2:27:54 PM - KrisBack To Top(89793)
Thank you for this, it was a great help and just saved me TONS of work.

Tuesday, December 14, 2021 - 2:53:16 AM - GustavBack To Top(89578)
Thank you Aaron. Great post and amazing effort.

Thursday, December 9, 2021 - 12:24:36 AM - Daniel PimentalBack To Top(89556)
Greatly appreciated, very well described and great in detail. Thank you for such a great effort. I got a clear idea about this subject.

Tuesday, August 17, 2021 - 10:03:29 PM - mergsTMBack To Top(89137)
There is an Independence Day obsewrved that was omitted. If July 4 is a Saturday, Friday is a holiday if it falls on a Sunday Monday is observed as the holiday.

I added some SQL in the HolidayDimension code below this line:

INSERT dbo.HolidayDimension(TheDate, HolidayText)
SELECT TheDate, HolidayText FROM s
UNION ALL
SELECT DATEADD(DAY, 1, TheDate), 'Black Friday'
FROM s WHERE HolidayText ='Thanksgiving Day'
ORDER BY TheDate;

to take care of it, these will insert the observed July 4th holidays into HolidayDimension:

-- Independence Day is a federal holiday. If July 4 is a Saturday, it is observed on Friday, July 3.
INSERT dbo.HolidayDimension(TheDate, HolidayText)
SELECT TheDate, 'Independence Day (observed)'
FROM dbo.DateDimension WHERE TheMonth = 7 AND TheDay = 3 AND TheDayName ='Friday';
-- Independence Day is a federal holiday. If July 4 is a Sunday, it is observed on Monday, July 5.
INSERT dbo.HolidayDimension(TheDate, HolidayText)
SELECT TheDate, 'Independence Day (observed)'
FROM dbo.DateDimension WHERE TheMonth = 7 AND TheDay = 5 AND TheDayName ='Monday';

Hope this helps someone.

Wednesday, July 28, 2021 - 12:32:12 AM - StudentBack To Top(89060)
How can I make The Week Column incremental i.e. it should not renew after completion of year and just continue adding up till last.

Friday, July 9, 2021 - 1:08:46 PM - Kelly SmithBack To Top(88980)
Aaron, thanks so much! I was able to modify this script to get it to do exactly what I needed! Cheers!

Saturday, May 1, 2021 - 5:52:15 AM - Glyn JonesBack To Top(88635)
Absolutely fantastic article and advice, I did this years ago and lost the scripts, thank you so much, this information and advice are priceless.

Friday, March 5, 2021 - 3:39:22 PM - Andrew FayBack To Top(88348)
Thank you! This worked like a charm! I really appreciate all of the detail

Wednesday, February 24, 2021 - 10:51:02 AM - Kurt JohnsonBack To Top(88292)
Thanks - that absolutely worked! Thought I had tried that approach, but must have had a brain cramp!

Tuesday, February 23, 2021 - 4:06:24 PM - Aaron BertrandBack To Top(88284)
Hey Kurt, it would just be the other way, right?

SELECT TOP (1) ... FROM
(
SELECT TOP (180) ...
WHERE TheDate <= TargetEndDate ...
ORDER BY TheDate DESC
) as BusinessDays
ORDER BY TheDate;

You could make it a little tidier by using window functions instead of 2 nested TOPs, but I tried to make as few changes to your logic as possible.

Tuesday, February 23, 2021 - 3:50:45 PM - Kurt JohnsonBack To Top(88283)
This article is incredible! I created both of the recommended tables, and a view called "USCalendar" for my queries.

This query calculates the end date, given a start date (03/01/21) and number of business days (180):

select Top 1 TheDate as EndDate
from (select Top 180 TheDate from USCalendar
Where TheDate>='03/01/2021'
And IsWeekend=0 and IsHoliday=0
Order By TheDate) as BusinessDays
Order By TheDate DESC

My only challenge is coming up with a way to reverse it - how do I find the start date if I know the target end date and number of business days?

Saturday, February 20, 2021 - 9:24:13 AM - JeanetteBack To Top(88270)
Agree with all the other comments, this is amazing and has helped me so much with the working day calculations I have to do in my work, with a row number column that numbers all the non-working days I can calculate the number of days between 2 dates or add the required number or days to a date to give the due date. As I don't have admin rights to our database I am not able to create functions in my working role but I found a formula for Easter dates that someone had posted on another site having adapted it from an Excel formula and this seems to work (adjust the last number for Easter Sunday or Good Friday:
EasterMonday = DATEADD(dd, ROUND(DATEDIFF(dd, '1899-12-30', DATEFROMPARTS(DATEPART(YEAR, d), 4, 1)) / 7.0 + ((19.0 * (DATEPART(YEAR, d) % 19) - 7) % 30) * 0.14, 0) * 7.0 - 6, -1)

Friday, December 18, 2020 - 5:53:25 PM - DBack To Top(87933)
Aaron, great post and lots of useful code. I think there's a bug in your recent update, though; it looks like the columns names and calculations are reversed for these 2:

Has53Weeks = CASE WHEN DATEPART(ISO_WEEK, TheLastOfYear) = 53 THEN 1 ELSE 0 END,
Has53ISOWeeks = CASE WHEN DATEPART(WEEK, TheLastOfYear) = 53 THEN 1 ELSE 0 END,

Wednesday, December 16, 2020 - 5:11:22 PM - Natalie HarperBack To Top(87922)
Hi Aaron,
This walk-through was PERFECT and is exactly what I was needing to implement for my team. Thank you so much!

Sunday, November 29, 2020 - 2:34:27 PM - FengBack To Top(87861)
Aaron,
Very great and useful article. I will implement into my report. I have one request, how to add sequential Day number (may be I could use Style 112 change to whole number data type), how to add sequential Year Quarter number, Year Month number? for calculate MTD, YTD, and QTD, use it's Sequential number would be very easy.
Thanks a lot and appreciate!
Feng Wang

Thursday, November 12, 2020 - 5:41:37 PM - Alex MahrouBack To Top(87794)
Hi Aaron, this really helped me create a calendar dimension table! I really appreciate it!

Wednesday, May 27, 2020 - 4:11:32 AM - yosemitesamBack To Top(85775)

Hi Aaron,

Excellent post, with a clear and straightforward explanation. I'm in the process of implementing this into an integration database of my own. Very, very useful.

However, I noticed a way of removing the need to have the Easter holiday calculations as a function and therefore used in cross apply statement. If you do this (below) then it produces all Easter holidays for all years in your dimension table as a simple SELECT

 ;WITH  easter AS
  (
    SELECT TheDate = DATEFROMPARTS(d.TheYear, [Month], [Day])
      FROM (SELECT [Month], [Day] = DaysToSunday + 28 - (31 * ([Month] / 4)),m.TheYear
    FROM (SELECT [Month] = 3 + (DaysToSunday + 40) / 44, DaysToSunday,dts.TheYear
        FROM (SELECT DaysToSunday = paschal - ((paschal.TheYear + (paschal.TheYear / 4) + paschal - 13) % 7),paschal.TheYear
            FROM (SELECT paschal = epact - (epact / 28),epact.TheYear
                FROM (SELECT epact = (24 + 19 * (TheYear % 19)) % 30,TheYear FROM dbo.DateDimension GROUP BY TheYear)  AS epact)
                AS paschal)
            AS dts)
        AS m)
    AS d
  )

SELECT easter.TheDate, 'Easter Sunday' FROM easter
UNION ALL SELECT DATEADD(DAY, -2, easter.TheDate), 'Good Friday'FROM easter
UNION ALL SELECT DATEADD(DAY,  1, easter.TheDate), 'Easter Monday'FROM easter
ORDER BY easter.TheDate


Friday, April 10, 2020 - 10:54:59 AM - Aaron BertrandBack To Top(85331)

Nice catch Tom, sorry about that.


Thursday, April 9, 2020 - 8:36:24 PM - Tom WickerathBack To Top(85321)

In the section towards the end of this article, "And then there's Easter", the following two lines produce an error:

UNION ALL SELECT DATEADD(DAY,-2,[Date]), 'Good Friday' FROM x

UNION ALL SELECT DATEADD(DAY, 1,[Date]), 'Easter Monday' FROM x

Msg 207, Level 16, State 1, Procedure GetEasterHolidays, Line 19 [Batch Start Line 376]
Invalid column name 'Date'.

I suspect the column name should be [TheDate].


Wednesday, April 1, 2020 - 3:28:02 PM - Aaron BertrandBack To Top(85245)

I've refreshed the scripts here to address many of the comments below. Main improvements:

  • Used multiple CTEs instead of #temp tables (though part of the text still refers to the #temp tables)
  • Suggested ways to address non-calendar-aligned fiscal quarters/years
  • Stopped using reserved keywords (just jammed The in front of most column names)
  • Removed all traces of using an integer key to represent the date
  • Switched to DATEFROMPARTS for start/end of period calculations (like first day of the year)
  • Added IsLeapYear, ISOYear, Has53Weeks, Has53ISOWeeks
  • Moved holidays to a separate table

I did not address every single comment (and actually made a few of my own comments obsolete), but can address some of those perhaps in a follow-up article later.


Tuesday, March 24, 2020 - 6:29:43 AM - EntrestingBack To Top(85175)

 

Kudos to this, please never remove this article! I have been using these scripts for years and end up always coming back to when needing new datedimension for a new reporting server. :)


Saturday, March 14, 2020 - 3:47:08 PM - JeremyBack To Top(85114)

I have to say this is a great Article!  Huge help.

The only comment I would make is to not use keywords as column names such as date, week and/or month.  It makes the code very hard to read and extra work to have to be including the [] around the column names so they don't get seen as keywords.

Thanks
jeremy


Thursday, March 5, 2020 - 12:13:04 AM - PaulKrBack To Top(84937)

Aaron, many thanks for this excellent post.  I ran this code in SQL Server 2019 (Developer), but couldn't understand what the code was doing when calculating the [DaysSuffix] column.  I didn't get what I expected.  Can you elablorate on what this column should contain.  The values i noticed in that column were : [Sunday ='nd', Monday ='rd', Tuesday ='th', Wednesday ='th', Thursday ='th', Friday ='th', Saturday ='st'].  (My OS, WIN 10 Pro, Locale = English (Canada)).  After further review, I noticed that the 'th' suffix showed up on other days as well including Saturday and Sunday.


Friday, February 14, 2020 - 6:10:57 PM - EzraBack To Top(84498)

This article solved a big problem that I had with my project, thanks


Sunday, December 29, 2019 - 11:05:43 AM - BoAnnaBack To Top(83552)

Hi,

This post actually resolved some of my dilemmas i have for a similar project :) 

I stil have a few decisions to make, but this helped figure out a few of them. This is a short description of what I got stuck on 

https://stackoverflow.com/questions/59483225/clustered-indexes-as-pk-or-pk-that-is-created-using-fks-as-a-serial-number?r=SearchResults


Friday, December 20, 2019 - 1:04:18 PM - John-S PretoriusBack To Top(83484)

Good day,

Thank you for the great topic on calenders, I have a challenge defining a standard day from 03am prev day to current 02:59:59


Tuesday, November 26, 2019 - 1:01:28 PM - ChaitanyaBack To Top(83201)

I am looking for a solution where I have table format at source e.g. NewTableName112019<MMYYYY>, So when I run my SSIS package it should dynamically select current month -1 (basically last month) automatically.

Help would be really appreciated


Wednesday, November 6, 2019 - 10:34:50 AM - Phil BarrBack To Top(82994)

This is wonderful.  Thank you so much for all the work you've put into this.  I'll let you know what enhancements I need as I begin to use it.


Friday, November 1, 2019 - 1:20:31 PM - NicolasBack To Top(82954)

Thanks!!! That helped me a lot! You are a genious.


Tuesday, October 1, 2019 - 1:41:44 PM - Daniel AdenijiBack To Top(82635)

Aaron Bertrand,

As always thanks for your articles.

Daniel Adeniji


Monday, September 16, 2019 - 4:57:51 PM - Marc ChurchBack To Top(82475)

Amazing!

You have saved me so much time with this.  Thanks you very much!!!


Thursday, August 29, 2019 - 12:29:16 PM - BenBack To Top(82185)

Hi Aaron,

I just wanted to say thank you for your date dimension/calendar table code.  This was really helpful in establishing some dynamic date ranges that I needed for a project.  I will definitely be following your blog for more tips and tricks!

Thanks!


Friday, March 1, 2019 - 10:53:19 AM - ElenaBack To Top(79176)

Very helpful! Thank you so much!

I do have a question though... As I try to set the holidays, I receive the message that I cannot change a computed column. I read your post several times, but do not seem to get where exactly to include updates. Could you be so kind and help me?


Thursday, February 7, 2019 - 11:25:03 AM - klmBack To Top(78984)

 Excellent!!!!  This was very helpful.


Thursday, January 17, 2019 - 10:40:07 AM - Aaron BertrandBack To Top(78801)

-- hugk,

/* after this section: */

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

INSERT #dim([date])
...
) AS y;
GO

/* add this: */

ALTER TABLE #dim ADD dimdate char(8);

;WITH calc AS 
(
  SELECT [date], dimdate,
    m = DATEADD(MONTH, CASE WHEN [day] > 15 THEN 1 ELSE 0 END, FirstOfMonth) 
  FROM #dim
)
UPDATE calc
  SET dimdate = CONVERT(char(3), DATENAME(MONTH,m))
    +' '+ CONVERT(char(4), m, 112);

/* once you add the dimdate column add dbo.DateDimension you should then just be able to add dimdate to the insert/select there. */


Thursday, January 17, 2019 - 9:47:58 AM - hugkBack To Top(78800)

Hi.

Thank you for this.

I would like to have a dimdate with the follow requirement.

2018-12-16 to 2019-01-15 = Jan 2019

2019 -01-16 to 2019-02-15 = Feb 2019

2019-02-16 to 2019-03-15 = Mar 2019

-

-

2019-11-16 to 2019-12-15 = Dec 2019

2019-12-16 to 2020-01-15 = Jan 2020

Please assist with any hint

Thanks


Monday, December 10, 2018 - 9:23:52 AM - BobbyBack To Top(78441)

Aaron, this is incredible. Thank you so much. I am struggling with 3 columns that I was asked to add and was hoping you can assist

Total Work Days in a Year Less Holiays?

Total Work Days till end of the Month, Less Holidays?

What current working day is today?

I have searched around with little help :( and was wonding if something like this could be achieved more simply?


Monday, December 3, 2018 - 9:33:25 PM - greg.lockeBack To Top(78400)

easy to follow. Thanks.

it was easy to add fiscal periods for off calendar annual cycles to the create TABLE #dim.  

 [FiscalY] AS CASE DATEPART(QUARTER,     [date]) WHEN 1 THEN DATEPART(YEAR,     [date]) WHEN 2 THEN DATEPART(YEAR,     [date]) WHEN 3 THEN DATEPART(YEAR,     [date]) + 1 WHEN 4 THEN  DATEPART(YEAR,     [date]) + 1 END,

  [FiscalQ] AS CASE DATEPART(QUARTER,  [date]) WHEN 1 THEN 3 WHEN 2 THEN 4 WHEN 3 THEN 1 WHEN 4 THEN  2 END,

  [FiscalM] AS CASE DATEPART(MONTH,  [date]) WHEN 7 THEN 1 WHEN 8 THEN 2 when 9 then 3 when 10 then 4 when 11 then 5 when 12 then 6 


Thursday, November 29, 2018 - 10:45:14 AM - LeonBack To Top(78361)

Very helpful post, thank you I learnt a lot looking through the SQL script, i'm just starting with Analysis Services and this is a big help!!!


Sunday, October 21, 2018 - 6:03:27 AM - Mike BaileyBack To Top(78005)

 Hi Aaron,

 

Thanks for the great article. Would it be possible for you to add an update to include setting Fiscal information for businesses that run on a Fiscal calendar - FiscalYear, FiscalMonth, FiscalWeek, FiscalDay?

 

Thanks,

Mike Bailey

 


Wednesday, September 19, 2018 - 11:24:58 AM - Joe CelkoBack To Top(77658)

Easter is one of my favorite holidays, not because I'm religious, but because I am a mathematician. It was originally created so that Passover and Easter would not conflict with each other. You could tell the Christians from the Jews. However, we want with two kinds of Christians and two kinds of Easter – Catholic and Orthodox. Decades ago I was trying to set up a calendar of the company was working at and we got Easter is a holiday. I had two junior programmers who were doing the work on this and one of them had found an algorithm for computing Easter. I told him don't bother with that rather tricky calculation; you are religious and you go to church every Sunday. So why don't you just ask your priest to hand you a list of all of the Good Fridays and Easters for the next 10 or 15 years? I thought this was a good example of the "don't reinvent the wheel" principle of programming. Unfortunately, I didn't know he was Greek Orthodox and that we were on the Catholic version of Easter. Sometimes they match, and sometimes they don't. 


Wednesday, September 19, 2018 - 8:22:17 AM - Wise Old ManBack To Top(77653)

 

Very nice.

One thing that is missing is a YOY date for those who "day align" their data like the hospitality industry.  An example of what that means is comparing the first Monday in January this year to the first Monday in January last year.  It's easy enough to just subtract 52 weeks, which always gives you the same day of the week; however the issue is that 365 (or 366) is not divisible by 7, so the week actually moves every 3-4 years.  It's very similar to the timeshare week problem of having weeks 1-53 every third or fourth year.  It would be interesting to see if there is an industry standard for adding that to the calendar table.


Thursday, August 30, 2018 - 7:23:20 AM - Aaron BertrandBack To Top(77343)

Paul Hunter, yes, Easter is not a holiday for all of us in the US, but I included it because there is a significant audience outside of the US *and* because it's an interesting problem that's a little harder to solve than typical holidays.

Anyway, your comments about Good Friday, that's why the Easter function has this:

UNIONALLSELECTDATEADD(DAY,-2,[Date]),'Good Friday'FROM x
 

Thursday, August 30, 2018 - 7:17:15 AM - Aaron BertrandBack To Top(77342)

Joshua, yeah, I've heard that argument before. But when I ask for an actual demonstration, or even a recipe for me to build my own demonstration, they always mysteriously move on to other things and never get back to me.

I feel like on modern computers this is like using tweezers to take bugs off your bumper to increase your gas mileage, but would love for you to show me an actual scenario where this does make a tangible difference. But not here. Build a real example, run real performance tests, blog about it. Without that, I'm going to be perfectly honest, the rest of us in the data community just see it as hand-wavy.

And still, even if there is a tangible difference in one part of the process, I'll probably still take that hit over the hits I know I'll take elsewhere - loss of validation, implicit conversions causing scans, and lots of sloppy compensatory explicit conversions riddling the code.


Wednesday, August 29, 2018 - 6:47:45 PM - Joshua PerryBack To Top(77341)

As one of those DW guys, I'll tell you why we want the date ID as an integer and not just a date...when you're dealing with those millions of rows, and you've properly optimized your DW to be CPU bound, you want to use a datatype that is native to the CPUfor filtering and sorting.  It makes a huge difference, and combined with different indexing and partitioning schemes, we can actually slice those millions of rows on the fly, without the need to build traditional multidimensional cubes, and that's the holy grail for BI self-service, because there is no need to have an analyst spend months building a model before the data can be consumed.  It goes back to what some of us learned back when CPUs were extremely slow...value types versus reference types, and the reason Visual Basic stores dates as integers with an epoch.  Integers as value types are processed by the CPU much faster than a SQL datetime.  We still use the datetime for certain functions as well, however, using the integer for inital sorting and filtering is much faster.


Thursday, August 2, 2018 - 12:37:36 PM - kiranBack To Top(76949)

 Very Nice


Tuesday, July 17, 2018 - 9:50:58 AM - Aaron BertrandBack To Top(76658)

Bob, I agree with you wholeheartedly, and actually have had that fight at work, and won.

But that is one DW person in a sea of people who will happily die on the sword of Kimball.

If you feel strongly about using a date, use date. It doesn't really change anything of substance here. I was just (uncharacteristically) being a conformist. 


Tuesday, July 17, 2018 - 8:45:10 AM - Bob ParisBack To Top(76657)

Hi,

I think it is a very good article. One point though, you said:

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.

I see this a LOT. I do not understand it because its a DATE and because dwguys do this then it has to be converted. A DATE is a DATE.

In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on. (From: https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-2017 )

I do not believe that a bad practice should be encouraged because many dwguys do it.


Friday, June 1, 2018 - 5:00:54 PM - JulieBack To Top(76076)

This was a huge help. Thank you so much. I'm working with school data so I updated the holiday based on another table. The whole thing worked like a charm and will save me hours of work with future queries.

 


Friday, March 23, 2018 - 4:16:09 PM - Paul HunterBack To Top(75513)

Thanks for the excellent tips.  Easter isn't normally a US holiday... unless your in the finicial trading sector, at which point the quesiton is "when is good friday".   If you can calculate Easter you can find good friday by backing up two days.  The other issue is "floaters"... any holiday that occurs on a specific day (i.e. New Years, US Independence, Veterans Day and Christmas) will float to the the preceeding/following week day when it falls during a weekend.  If the holiday falls on a Saturday it floats to Friady, if it falls on Sunday it floats to Monday.  I assume there are similar rules for non-US holidays such as Boxing Day.

 


Tuesday, December 5, 2017 - 2:43:07 PM - Devang MistryBack To Top(73652)

 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 BertrandBack To Top(73301)

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 - KirkBack To Top(73298)

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-MaaritBack To Top(69505)

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

Anne-Maarit

 


Tuesday, November 7, 2017 - 5:20:05 PM - alanBack To Top(69393)

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

 

Kudos!

 


Friday, November 3, 2017 - 5:11:07 AM - BajkeBack To Top(69144)

Please use this link https://www.timeanddate.com/date/weeknumber.html

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 2, 2017 - 4:44:22 PM - Greg RobidouxBack To Top(69120)

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 2, 2017 - 4:25:53 PM - BajkeBack To Top(69117)

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

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


Thursday, November 2, 2017 - 3:50:14 PM - Greg RobidouxBack To Top(69114)

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 2, 2017 - 1:44:24 PM - BajkeBack To Top(69107)

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

Best Regards,

 


Saturday, September 9, 2017 - 11:10:16 PM - stephen E OkalaBack To Top(66117)

 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 CelkoBack To Top(51544)

 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 - MazBack To Top(51528)

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 - KeviMBack To Top(51334)

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 MoraschiBack To Top(46333)

It is an extemely useful script.
Kudos

Davide.


Monday, February 13, 2017 - 9:30:10 AM - JoeBack To Top(46280)

 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 - RobertBack To Top(45506)

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 - BrianBack To Top(43613)

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 GBack To Top(43564)

 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 DaveyBack To Top(43313)

 

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 7, 2016 - 10:03:43 AM - ErikBack To Top(43275)

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 2, 2016 - 3:05:42 AM - Gavin KelmanBack To Top(43255)

 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 2, 2016 - 3:59:36 PM - vishnuBack To Top(43034)

 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 SchneiderBack To Top(41079)

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 EverestBack To Top(40406)

Nice, Aaron. Thanks! 

 


Tuesday, October 20, 2015 - 7:30:03 PM - Henry StinsonBack To Top(38949)

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 - RenanBack To Top(38946)

Nice tip.

Very easy to use and customize.

Thanks.

Renan