<

SQL Server Calendar Table

Published on
80,075 Points
40,175 Views
39 Endorsements
Last Modified:
Awarded
Community Pick
Jim Horn
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.
A demonstration of SQL Server T-SQL script that will build a calendar table, and demonstrate ways to easily query it to perform what would otherwise be complex date expressions.

The entire code can be copied at the very bottom of this article.
Flava Flav
Flava Flav may know what time it is, but can he calculate custom business days between any two dates?  
This is the first in a series of articles on SQL Server handling dates.  Here are the next articles.. 
  • SQL Server Calendar Table:  Tips and Tricks, common stored procedures and functions to manipulate the calendar table when querying other data. 
  • SQL Server Calendar Table:  Fiscal Years, almost every company measures results in some kind of year whether it be calendar year or some other definition known as a Fiscal Year.  This article shows how to build on the SQL Server Calendar Table article to handle the Fiscal Year.

Benefits of a calendar table:
  • Stores all holidays and events that cannot be calculated using a SQL Server expression.
  • Ability to analyze days based on custom events and indicators.  I once worked at a casino that constantly analyzed money in based on day of week, weather, holidays, concerts, promotions, restaurant specials, presence of Elvis or Marilyn Monroe impersonators (do not doubt me on this), and local events.
  • Pre-writing of common report row and column footers based on preferred date language, and increased query performance in not having to re-generate it every time.

Benefits not in this article, but I will write future articles if I get enough feedback:
  • For data warehousing, the use of ID's instead of dates for increased query performance.  Not discussed in this article.

Everyone repeat after me:
I do solemnly swear that if this information is found useful and actually deployed into production, to make a contribution to the Jim Horn Good Guy Retirement Fund, care of Experts Exchange.  That, or click the Yes button at the end of this article to state that this article was helpful.
Now let's get this show started.
 

Create the table

Let's build a table with rows for 2000-01-01 through 2020-12-31.

Couple of notes:
  • SET DATEFIRST 7 sets the week begins on Sunday and ends on Saturday, which will affect grouping of days into weeks.  1 is Sunday, 2 is Monday, all the way to 7 is Saturday.  If your needs are different, this value can be set anywhere from 1 (Monday) to 7 (Sunday).  To see the current datefirst, execute --> SELECT @@DATEFIRST
  • Many of the values related to year, month, and week could also be populated into tables called year, month, and week, but for this article I'll only use a table called days.
  • Many columns prefixed 'continuous...' exist to facilitate querying months, weeks, days, etc. that cross over a year boundary and can be manipulated using simple math instead of frequent use of the DATEADD() function. 
 
/*
Build a table of days
08-22-13  Jim Horn  Original
02-22-15  Jim Horn  Multiple fixes to holidays, added continous_ columns to set up second article
09-03-15  Jim Horn  Added more events, changed tab to three spaces so it renders better in EE's article designer
09-17-15  Jim Horn  Moved the start date back to 2000-01-01 to facilitate queries on database AdventureWorksDW2012.
*/

SET NOCOUNT ON
GO
  
IF EXISTS (SELECT name FROM sys.databases WHERE name='calendar') 
   DROP DATABASE calendar
GO

CREATE DATABASE calendar
GO

USE calendar  
GO

-- 1=Sunday to 7=Saturday
SET DATEFIRST 7
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name='days') 
   DROP TABLE days
GO

CREATE TABLE days (
   PKDate date NOT NULL PRIMARY KEY CLUSTERED, 
   -- Years
   calendar_year smallint,
   -- Quarters 
   calendar_quarter tinyint, 
   calendar_quarter_desc varchar(10), 
   -- Months
   calendar_month tinyint, 
   calendar_month_name_long varchar(30), 
   calendar_month_name_short varchar(10), 
   -- Weeks
   calendar_week_in_year tinyint, 
   calendar_week_in_month tinyint,  
   -- Days
   calendar_day_in_year smallint, 
   calendar_day_in_week tinyint,  -- The first of the month 
   calendar_day_in_month tinyint, 
   mdy_name_long varchar(30), 
   mdy_name_long_with_suffix varchar(30), 
   day_name_long varchar(10), 
   day_name_short varchar(10), 
   -- Continuous Y/M/D, starts with the first day = 1 and keeps going.  Used for various dateadd functions.
   continuous_year tinyint,
   continuous_quarter smallint, 
   continuous_month smallint, 
   continuous_week smallint, 
   continuous_day int, 
   -- Custom
   description varchar(100), 
   is_weekend tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_holiday tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_workday tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_event tinyint)        -- Used to indicate any special event days. 
GO

-- Create the table, with dates ranging from 2010 to 2020.  Change to suit your needs.
Declare @dt_start date = '2000-01-01', @dt_end date = '2020-12-31', @total_days int, @i int = 0
SELECT @total_days = DATEDIFF(d, @dt_start, @dt_end) 

WHILE @i <= @total_days
   begin
   INSERT INTO days (PKDate) 
   SELECT CAST(DATEADD(d, @i, @dt_start) as DATE) 

   SET @i = @i + 1
   end

Open in new window


UPDATE column values with T-SQL functions

These values can be generated with single SQL Server functions
 
-- These values can be generated with single SQL Server functions
UPDATE days
SET 
   calendar_year = YEAR(PKDate), 
   calendar_quarter = DATEPART(q, PKDate),
   calendar_month = DATEPART(m, PKDate), 
   calendar_week_in_year = DATEPART(WK, PKDate), 
   calendar_day_in_year = DATEPART(dy, PKDate), 
   calendar_day_in_week = DATEPART(Weekday, PKDate),
   calendar_day_in_month = DATEPART(d, PKDate),
   day_name_long = datename(weekday, PKDate)

-- These values need either logic, customization in functions, or customization based on client needs.
UPDATE days
SET 
   is_weekend = CASE DATEPART(weekday, PKDate) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END, 
   calendar_quarter_desc =  'Q' + CAST(calendar_quarter as char(1)) + ' ' + CAST(calendar_year as char(4)), 
   calendar_month_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_year as CHAR(4)),
   mdy_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + ', '  + CAST(calendar_year as CHAR(4)),
   day_name_short = LEFT(datename(weekday, PKDate),3)

Open in new window



UPDATE column values with custom T-SQL expressions with conditions

These values need either logic, customization in functions, or customization based on client needs.
 
-- These values need either logic, customization in functions, or customization based on client needs.
UPDATE days
SET 
   is_weekend = CASE DATEPART(weekday, PKDate) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END, 
   calendar_quarter_desc =  'Q' + CAST(calendar_quarter as char(1)) + ' ' + CAST(calendar_year as char(4)), 
   calendar_month_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_year as CHAR(4)),
   mdy_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + ', '  + CAST(calendar_year as CHAR(4)),
   day_name_short = LEFT(datename(weekday, PKDate),3)

UPDATE days
SET calendar_week_in_month = 
CASE 
   WHEN calendar_day_in_month BETWEEN 1 AND 7 THEN 1
   WHEN calendar_day_in_month BETWEEN 8 AND 14 THEN 2
   WHEN calendar_day_in_month BETWEEN 15 AND 21 THEN 3
   WHEN calendar_day_in_month BETWEEN 22 AND 28 THEN 4
   ELSE 5
END

Open in new window


Calendar month names, where clients will want an abbreviated version of the month that may not always be the first three letters of the month.
 
-- Month name:  The first three letters of the month.
UPDATE days
SET calendar_month_name_short = LEFT(DATENAME(month, PKDate),3)

/*
-- If the above doesn't work and you want to spell it out, then use this..
UPDATE days
SET calendar_month_name_short = CASE calendar_month
   WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
   WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'June'
   WHEN 7 THEN 'July' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
   WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' END
*/

Open in new window


Fancy schmancy full date column, which adds the suffix st, nd, rd, or th to the day.
-- Fancy schmancy full date column, which adds the suffix st, nd, rd, or th to the day.
UPDATE days
SET   mdy_name_long_with_suffix = CASE RIGHT(CAST(calendar_day_in_month as varchar(2)), 1) 
   WHEN '1' THEN DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'st, '  + CAST(calendar_year as CHAR(4))
   WHEN '2' THEN DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'nd, '  + CAST(calendar_year as CHAR(4))
   WHEN '3' THEN DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'rd, '  + CAST(calendar_year as CHAR(4))
   ELSE DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'th, '  + CAST(calendar_year as CHAR(4)) END

Open in new window


Continuous columns, which do not reset back to 1 at the beginning of the next year.  Used for T-SQL expressions that will be explained later. 

UPDATE days	
SET
   continuous_year = DATEDIFF(year, @dt_start, PKDate) + 1, 
   continuous_quarter = DATEDIFF(QUARTER , @dt_start, PKDate) + 1, 
   continuous_month = DATEDIFF(month, @dt_start, PKDate) + 1, 
   continuous_week = DATEDIFF(week, @dt_start, PKDATE) + 1,
   continuous_day = DATEDIFF(day, @dt_start, PKDATE) + 1

Open in new window



Holidays and Events

Fixed holidays which are always on the same date of every year, Monday through Friday.  These are a subset of all holidays, so clients will have to determine all of their holidays and add them in this section.

-- HOLIDAYS WHERE THE DAY IS ALWAYS THE SAME
-- Fixed Holidays, Mondays through Fridays.   
-- (see http://www.cute-calendar.com/category/federal-holidays-in-the-united-states.html) 

UPDATE days
SET is_holiday = 1, description = 'American Martin Luther King Jr. Birthday' -- third Monday in January
WHERE calendar_month = 1 AND calendar_week_in_month = 3 AND calendar_day_in_week = 2

UPDATE days
SET is_holiday = 1, description = 'American Presidents Day '  -- third Monday in February
WHERE calendar_month = 2 AND calendar_week_in_month = 3 AND calendar_day_in_week = 2

UPDATE days
SET is_holiday = 1, description = 'American Labor Day' -- first Monday of September
WHERE calendar_month = 9 AND calendar_week_in_month = 1 AND calendar_day_in_week = 2

UPDATE days
SET is_holiday = 1, description = 'American Columbus Day' -- second Monday in October
WHERE calendar_month = 10 AND calendar_week_in_month = 2 AND calendar_day_in_week = 2

UPDATE days
SET is_holiday = 1, description = 'American Thanksgiving' -- fourth Thursday in November plus the adjoining Friday
WHERE (calendar_month = 11 AND calendar_week_in_month = 4 AND calendar_day_in_week = 5) 

UPDATE days
SET is_holiday = 1, description = 'Day after American Thanksgiving' -- fourth Thursday in November plus the adjoining Friday
WHERE (calendar_month = 11 AND calendar_week_in_month = 4 AND calendar_day_in_week = 6)

Open in new window


Fixed Holidays by specific date, such as July 4th, but if on a weekend would force either the previous Friday or following Monday to be the holiday.
 
-- Fixed Holidays by specific date, such as July 4th, but if on a weekend would force either the previous Friday or following Monday to be the holiday.
-- HOLIDAYS WHERE THE DAYS MAY CHANGE
UPDATE days 
SET is_holiday = 1, description = 'New Year''s Day' -- - January 1st
WHERE (calendar_month = 1 AND calendar_day_in_month = 1)	-- 1/1 when 1/1 is Monday through Friday

UPDATE days 
SET is_holiday = 1, description = 'New Year''s Day observed' -- - January 1st
WHERE 
   (calendar_month = 12 AND calendar_day_in_month = 31 AND calendar_day_in_week = 6) OR		-- Friday 12/31 when 1/1 is Saturday
   (calendar_month = 1 AND calendar_day_in_month = 2 AND calendar_day_in_week = 2) 			-- Monday 1/2 when 1/1 is Sunday

-- American Independence Day - July 4th – Or if 7/4 falls on a Saturday, then observe on Friday; if it falls on a Sunday, then observe on Monday
UPDATE days 
SET is_holiday = 1, description = 'American Independence Day'
WHERE (calendar_month = 7 AND calendar_day_in_month = 4)

UPDATE days 
SET is_holiday = 1, description = 'American Independence Day observed'
WHERE 
   (calendar_month = 7 AND calendar_day_in_month = 3 AND calendar_day_in_week = 6) OR 
   (calendar_month = 7 AND calendar_day_in_month = 5 AND calendar_day_in_week = 2) 

-- American Veterans Day - November 11th – Or if 11/11 falls on a Saturday, then observe on Friday; if it falls on a Sunday, then observe on Monday
UPDATE days 
SET is_holiday = 1, description = 'American Veterans Day' 
WHERE (calendar_month = 11 AND calendar_day_in_month = 11)

UPDATE days 
SET is_holiday = 1, description = 'American Veterans Day observed' 
WHERE (calendar_month = 11 AND calendar_day_in_month = 10 AND calendar_day_in_week = 6) OR 
   (calendar_month = 11 AND calendar_day_in_month = 12 AND calendar_day_in_week = 2) 
   
-- Christmas - December 24th and 25th - Or if one or the other falls on a Saturday, then observe on Friday; if it falls on a Sunday, then observe on Monday
UPDATE days 
SET is_holiday = 1, description = 'Christmas' 
WHERE (calendar_month = 12 AND calendar_day_in_month = 25)

UPDATE days 
SET is_holiday = 1, description = 'Christmas observed' 
WHERE (calendar_month = 12 AND calendar_day_in_month = 24 AND calendar_day_in_week = 6) OR
   (calendar_month = 12 AND calendar_day_in_month = 26 AND calendar_day_in_week = 2)  

Open in new window


Variable holidays, such as 'The last Monday in May'
-- Memorial Day
UPDATE days
SET is_holiday = 1, description = 'Memorial Day' -- last Monday in May --select *
FROM days 
   JOIN  (
      SELECT max(calendar_week_in_month) WkInMonth , year(pkdate) as Yr
      FROM days d 
      WHERE d.calendar_month = 5 and (calendar_day_in_week = 2)
      GROUP BY year(PKDate)) D on D.WkInMonth = days.calendar_week_in_month and D.Yr = year(pkdate) and days.calendar_month = 5 AND calendar_day_in_week = 2 -- Monday

-- Set the non-holiday days
UPDATE days SET is_holiday = 0 WHERE is_holiday IS NULL
UPDATE days SET is_workday = CASE WHEN is_weekend = 0 AND is_holiday = 0 THEN 1 ELSE 0 END

Open in new window


Results for American Independence Day; not always on July 4th.Client-defined special events, which I'm defining as not a workday or a holiday, but a day of interest that the client may want to track for data analysis. Common for retail operations that want to know what affect these events had on business.
 
-- Client-defined special events

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
   DROP TABLE #tmp
GO

CREATE TABLE #tmp (PKDate date, description varchar(100)) 

INSERT INTO #tmp (PKDate, description) 
VALUES 
   ('2015-01-02', 'Frank Caliendo'), 
   ('2015-01-05', 'Rock of the 80''s'), 
   ('2015-01-15', 'Smokey Robinson Pre-Show Wine & Smoked Cheese Tasting'), 
   ('2015-01-16', 'Smokey Robinson'), 
   ('2015-01-22', 'Rewind Fest 2015:  Psychedelic Furs and More'), 

   ('2015-02-04', 'ZZ Top with Special Guest Blackberry Smoke'), 
   ('2015-02-07', 'Block Party With Kool & The Gang And More'), 
   ('2015-02-13', 'Thunder Vibes Reggae Festival'), 
   ('2015-02-14', 'Michael McDonald and Boz Scaggs'), 
   ('2015-02-16', 'Marco Antonio Solis Y Camilia - La Experiencia Tour'), 

   ('2015-02-26', 'Gladiator Challenge: Collision Course'), 
   ('2015-02-27', 'Rick Springfield'), 
   ('2015-03-04', 'The Thunder Down Under Australian Nudie Revue'), 
   ('2015-03-05', 'The Cosplay Five Sings The Hits'), 
   ('2015-03-10', 'Strawberry Music Fest - Amy''s Orchid Late Nite Lounge'), 

   ('2015-03-27', 'Kenny G: The Rock Opera'), 
   ('2015-03-28', 'Purple Ones - Tribute to the Music of Prince'), 
   ('2015-03-31', 'Steel Slinky - Party Band'), 
   ('2015-04-05', 'Chains Required - Horn driven R&B'), 
   ('2015-04-17', 'Cheezy Poofs = A Dash of alt-rock with soul and funk'), 

   ('2015-04-23', 'Rewind Fest 2015:  Psychedelic Furs and More'), 
   ('2015-04-24', 'Shaq''s All-Star Comedy Jam'), 
   ('2015-05-15', 'Peppermint Patty and Mustafa''s All Star Jamacian Steel Drum Band'), 
   ('2015-05-21', 'IFC Caged Combat'), 
   ('2015-06-15', 'The Fabulous Jewish Magician Signumd J. Goldstein'), 

   ('2015-06-30', 'Pearl City Marathon'), 
   ('2015-07-20', 'Tough Mudder'), 
   ('2015-09-05', 'Huey Lewis and The News' ), 
   ('2015-09-18', 'Sarah Colonna and feature act Jeff Bodart'), 
   ('2015-09-19', 'Sarah Colonna and feature act Jeff Bodart'), 

   ('2015-09-12', 'Duc Huy: 50 Years of Love and Music'), 
   ('2015-09-25', 'ABBACADABRA–The Ultimate ABBA Tribute'), 
   ('2015-09-26', 'Last Comic Standing Live Tour'), 
   ('2015-09-09', 'America''s Got Talent Live: The All-Stars Tour!'), 
   ('2015-10-29', 'Flashdance – The Musical'), 

   ('2015-10-10', 'Kenny Rogers' ), 
   ('2015-10-31', 'Sinners & Saints Halloween Party'), 
   ('2015-11-20', 'Donny & Marie Celebrating the Holidays'), 
   ('2015-11-21', 'Donny & Marie Celebrating the Holidays')

-- Set the days with events
UPDATE d
SET d.is_event = 1, d.Description = t.description
FROM #tmp t
   JOIN days d ON t.PKDate = d.PKDate

-- Set the days without events
UPDATE days SET is_event = 0 WHERE is_event IS NULL

Open in new window


Now we're done modifying the table, which will look like this (first six columns only)

Return set


Answer common date expression questions

Now that we have the table built, let's answer some common date questions.


Business days between two dates

A frequent question is to calculate all business days between two dates, usually the start of the month and yesterday. Business days are not handled in SQL Server, but now that we've defined it, it's an easy T-SQL call.

This statement returns the number of business days ...
 
Declare @dtStart as date = '2013-01-01', @dtEnd as date = '2013-01-20'
SELECT SUM(is_workday) 
FROM days
WHERE PKDate BETWEEN @dtStart and @dtEnd

Open in new window


... and this statement returns each day as a column.
 
SELECT PKDate
FROM days
WHERE PKDate BETWEEN @dtStart and @dtEnd AND is_workday = 1

Open in new window

Return set


Previous week boundaries


Another frequent question is to take the current day, and define the boundaries of the previous week.
 
Declare @dt date = CAST(GETDATE() as date) , @dtWeekBegin as date, @dtWeekEnd as date
SELECT MIN(PKDate), MAX(PKDate)
FROM days
WHERE continuous_week = (SELECT continuous_week - 1 FROM days WHERE PKDate = @dt)

Open in new window


Current week
 
Declare @dt date = CAST(GETDATE() as date) , @dtWeekBegin as date, @dtWeekEnd as date

SELECT MIN(PKDate), MAX(PKDate)
FROM days
WHERE continuous_week = (SELECT continuous_week FROM days WHERE PKDate = @dt)

Open in new window

Return set


List of weeks as a single row


Another frequent question is to render each week as a row in a table, with a cosmetic day range to display to the user, and a number to handle sorting. Fancy schmancy.
 
;
WITH 
   low AS (SELECT calendar_year, calendar_week_in_year, Min(PKDate) as min_date FROM days GROUP BY calendar_year, calendar_week_in_year),  
   high AS (SELECT calendar_year, calendar_week_in_year, Max(PKDate) as max_date FROM days GROUP BY calendar_year, calendar_week_in_year), 
   low_long_name AS (SELECT days.calendar_year, days.calendar_week_in_year, days.mdy_name_long_with_suffix FROM days JOIN low ON low.min_date = days.PKDate), 
   high_long_name AS (SELECT days.calendar_year, days.calendar_week_in_year, days.mdy_name_long_with_suffix FROM days JOIN high ON high.max_date = days.PKDate)
select distinct days.calendar_year, days.calendar_week_in_year, CAST(low_long_name.mdy_name_long_with_suffix as varchar(20)) + ' - ' + CAST(high_long_name.mdy_name_long_with_suffix as varchar(20)) as week_day_range   --  low.mdy_name_long_with_suffix + ' - ' + high.mdy_name_long_with_suffix
FROM days
   JOIN low_long_name ON days.calendar_year = low_long_name.calendar_year AND  days.calendar_week_in_year = low_long_name.calendar_week_in_year
   JOIN high_long_name ON days.calendar_year = high_long_name.calendar_year AND days.calendar_week_in_year = high_long_name.calendar_week_in_year
ORDER BY calendar_year, calendar_week_in_year

Open in new window

Return setThank you for reading my article, feel free to leave me some feedback regarding the content or to recommend future work. 
If you liked this article please click the 'Good Article' button.

I look forward to hearing from you. -  Jim - ( LinkedIn ) ( Twitter )


The entire code in one block is here
/*
Build a table of days
08-22-13  Jim Horn  Original
02-22-15  Jim Horn  Multiple fixes to holidays, added continous_ columns to set up second article
09-03-15  Jim Horn  Added more events, changed tab to three spaces so it renders better in EE's article designer
09-17-15  Jim Horn  Moved the start date back to 2000-01-01 to facilitate queries on database AdventureWorksDW2012.
*/

SET NOCOUNT ON
GO
  
IF EXISTS (SELECT name FROM sys.databases WHERE name='calendar') 
   DROP DATABASE calendar
GO

CREATE DATABASE calendar
GO

USE calendar  
GO

-- 1=Sunday to 7=Saturday
SET DATEFIRST 7
GO

IF EXISTS(SELECT * FROM sys.tables WHERE name='days') 
   DROP TABLE days
GO

CREATE TABLE days (
   PKDate date NOT NULL PRIMARY KEY CLUSTERED, 
   -- Years
   calendar_year smallint,
   -- Quarters 
   calendar_quarter tinyint, 
   calendar_quarter_desc varchar(10), 
   -- Months
   calendar_month tinyint, 
   calendar_month_name_long varchar(30), 
   calendar_month_name_short varchar(10), 
   -- Weeks
   calendar_week_in_year tinyint, 
   calendar_week_in_month tinyint,  
   -- Days
   calendar_day_in_year smallint, 
   calendar_day_in_week tinyint,  -- The first of the month 
   calendar_day_in_month tinyint, 
   mdy_name_long varchar(30), 
   mdy_name_long_with_suffix varchar(30), 
   day_name_long varchar(10), 
   day_name_short varchar(10), 
   -- Continuous Y/M/D, starts with the first day = 1 and keeps going.  Used for various dateadd functions.
   continuous_year tinyint,
   continuous_quarter smallint,
   continuous_month smallint, 
   continuous_week smallint, 
   continuous_day int, 
   -- Custom
   description varchar(100), 
   is_weekend tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_holiday tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_workday tinyint,      -- Tinyint and not bit so you can add the 1's. 
   is_event tinyint)        -- Used to indicate any special event days. 
GO

-- Create the table, with dates ranging from 2010 to 2020.  Change to suit your needs.
Declare @dt_start date = '2000-01-01', @dt_end date = '2020-12-31', @total_days int, @i int = 0
SELECT @total_days = DATEDIFF(d, @dt_start, @dt_end) 

WHILE @i <= @total_days
   begin
   INSERT INTO days (PKDate) 
   SELECT CAST(DATEADD(d, @i, @dt_start) as DATE) 

   SET @i = @i + 1
   end

-- These values can be generated with single SQL Server functions
UPDATE days
SET 
   calendar_year = YEAR(PKDate), 
   calendar_quarter = DATEPART(q, PKDate),
   calendar_month = DATEPART(m, PKDate), 
   calendar_week_in_year = DATEPART(WK, PKDate), 
   calendar_day_in_year = DATEPART(dy, PKDate), 
   calendar_day_in_week = DATEPART(Weekday, PKDate),
   calendar_day_in_month = DATEPART(d, PKDate),
   day_name_long = datename(weekday, PKDate)

-- These values need either logic, customization in functions, or customization based on client needs.
UPDATE days
SET 
   is_weekend = CASE DATEPART(weekday, PKDate) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END, 
   calendar_quarter_desc =  'Q' + CAST(calendar_quarter as char(1)) + ' ' + CAST(calendar_year as char(4)), 
   calendar_month_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_year as CHAR(4)),
   mdy_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + ', '  + CAST(calendar_year as CHAR(4)),
   day_name_short = LEFT(datename(weekday, PKDate),3)

UPDATE days
SET calendar_week_in_month = 
CASE 
   WHEN calendar_day_in_month BETWEEN 1 AND 7 THEN 1
   WHEN calendar_day_in_month BETWEEN 8 AND 14 THEN 2
   WHEN calendar_day_in_month BETWEEN 15 AND 21 THEN 3
   WHEN calendar_day_in_month BETWEEN 22 AND 28 THEN 4
   ELSE 5
END

-- Month name:  The first three letters of the month. 
UPDATE days
SET calendar_month_name_short = LEFT(DATENAME(month, PKDate),3)

/*
-- If the above doesn't work and you want to spell it out, then use this..
UPDATE days
SET calendar_month_name_short = CASE calendar_month
   WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
   WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'June'
   WHEN 7 THEN 'July' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
   WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' END
*/

-- Fancy schmancy full date column, which adds the suffix st, nd, rd, or th to the day.
UPDATE days
SET   mdy_name_long_with_suffix = CASE RIGHT(CAST(calendar_day_in_month as varchar(2)), 1) 
   WHEN '1' THEN DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'st, '  + CAST(calendar_year as CHAR(4))
   WHEN '2' THEN DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'nd, '  + CAST(calendar_year as CHAR(4))
   WHEN '3' THEN DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'rd, '  + CAST(calendar_year as CHAR(4))
   ELSE DATENAME(m, PKDate) + ' ' + CAST(calendar_day_in_month as varchar(2)) + 'th, '  + CAST(calendar_year as CHAR(4)) END

-- CONTINUOUS YEARS

UPDATE days	
SET
   continuous_year = DATEDIFF(year, @dt_start, PKDate) + 1, 
   continuous_quarter = DATEDIFF(quarter, @dt_start, PKDate) + 1, 
   continuous_month = DATEDIFF(month, @dt_start, PKDate) + 1, 
   continuous_week = DATEDIFF(week, @dt_start, PKDATE) + 1,
   continuous_day = DATEDIFF(day, @dt_start, PKDATE) + 1

-- HOLIDAYS
-- HOLIDAYS
-- HOLIDAYS

-- Fixed holidays which are always on the same date of every year, Monday through Friday. 
 
-- HOLIDAYS WHERE THE DAY IS ALWAYS THE SAME
-- Fixed Holidays, Mondays through Fridays.   
-- (see http://www.cute-calendar.com/category/federal-holidays-in-the-united-states.html) 

UPDATE days
SET is_holiday = 1, description = 'American Martin Luther King Jr. Birthday' -- third Monday in January
WHERE calendar_month = 1 AND calendar_week_in_month = 3 AND calendar_day_in_week = 2

UPDATE days
SET is_holiday = 1, description = 'American Presidents Day '  -- third Monday in February
WHERE calendar_month = 2 AND calendar_week_in_month = 3 AND calendar_day_in_week = 2

UPDATE days
SET is_holiday = 1, description = 'American Labor Day' -- first Monday of September
WHERE calendar_month = 9 AND calendar_week_in_month = 1 AND calendar_day_in_week = 2

UPDATE days
SET is_holiday = 1, description = 'American Columbus Day' -- second Monday in October
WHERE calendar_month = 10 AND calendar_week_in_month = 2 AND calendar_day_in_week = 2

UPDATE days
SET is_holiday = 1, description = 'American Thanksgiving' -- fourth Thursday in November plus the adjoining Friday
WHERE (calendar_month = 11 AND calendar_week_in_month = 4 AND calendar_day_in_week = 5) 

UPDATE days
SET is_holiday = 1, description = 'Day after American Thanksgiving' -- fourth Thursday in November plus the adjoining Friday
WHERE (calendar_month = 11 AND calendar_week_in_month = 4 AND calendar_day_in_week = 6)

-- Fixed Holidays by specific date, such as July 4th, but if on a weekend would force either the previous Friday or following Monday to be the holiday.
-- HOLIDAYS WHERE THE DAYS MAY CHANGE
UPDATE days 
SET is_holiday = 1, description = 'New Year''s Day' -- - January 1st
WHERE (calendar_month = 1 AND calendar_day_in_month = 1)	-- 1/1 when 1/1 is Monday through Friday

UPDATE days 
SET is_holiday = 1, description = 'New Year''s Day observed' -- - January 1st
WHERE 
   (calendar_month = 12 AND calendar_day_in_month = 31 AND calendar_day_in_week = 6) OR		-- Friday 12/31 when 1/1 is Saturday
   (calendar_month = 1 AND calendar_day_in_month = 2 AND calendar_day_in_week = 2) 			-- Monday 1/2 when 1/1 is Sunday

-- American Independance Day - July 4th – Or if 7/4 falls on a Saturday, then observe on Friday; if it falls on a Sunday, then observe on Monday
UPDATE days 
SET is_holiday = 1, description = 'American Independance Day'
WHERE (calendar_month = 7 AND calendar_day_in_month = 4)

UPDATE days 
SET is_holiday = 1, description = 'American Independance Day observed'
WHERE 
   (calendar_month = 7 AND calendar_day_in_month = 3 AND calendar_day_in_week = 6) OR 
   (calendar_month = 7 AND calendar_day_in_month = 5 AND calendar_day_in_week = 2) 

-- American Veterans Day - November 11th – Or if 11/11 falls on a Saturday, then observe on Friday; if it falls on a Sunday, then observe on Monday
UPDATE days 
SET is_holiday = 1, description = 'American Veterans Day' 
WHERE (calendar_month = 11 AND calendar_day_in_month = 11)

UPDATE days 
SET is_holiday = 1, description = 'American Veterans Day observed' 
WHERE (calendar_month = 11 AND calendar_day_in_month = 10 AND calendar_day_in_week = 6) OR 
   (calendar_month = 11 AND calendar_day_in_month = 12 AND calendar_day_in_week = 2) 
   
-- Christmas - December 24th and 25th - Or if one or the other falls on a Saturday, then observe on Friday; if it falls on a Sunday, then observe on Monday
UPDATE days 
SET is_holiday = 1, description = 'Christmas' 
WHERE (calendar_month = 12 AND calendar_day_in_month = 25)

UPDATE days 
SET is_holiday = 1, description = 'Christmas observed' 
WHERE (calendar_month = 12 AND calendar_day_in_month = 24 AND calendar_day_in_week = 6) OR
   (calendar_month = 12 AND calendar_day_in_month = 26 AND calendar_day_in_week = 2)  


-- Variable Holidays, such as 'The last Monday in May' (thanks to expert aflcio-hit http://www.experts-exchange.com/members/aflcio-hit.html )

-- Memorial Day
UPDATE days
SET is_holiday = 1, description = 'Memorial Day' -- last Monday in May --select *
FROM days 
   JOIN  (
      SELECT max(calendar_week_in_month) WkInMonth , year(pkdate) as Yr
      FROM days d 
      WHERE d.calendar_month = 5 and (calendar_day_in_week = 2)
      GROUP BY year(PKDate)) D on D.WkInMonth = days.calendar_week_in_month and D.Yr = year(pkdate) and days.calendar_month = 5 AND calendar_day_in_week = 2 -- Monday

-- Set the non-holiday days
UPDATE days SET is_holiday = 0 WHERE is_holiday IS NULL
UPDATE days SET is_workday = CASE WHEN is_weekend = 0 AND is_holiday = 0 THEN 1 ELSE 0 END


-- Client-defined special events, which I'm defining as not a workday or a holiday, 
--   but a day of interest that the client may want to track for data analysis. 

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
   DROP TABLE #tmp
GO

CREATE TABLE #tmp (PKDate date, description varchar(100)) 

INSERT INTO #tmp (PKDate, description) 
VALUES 
   ('2015-01-02', 'Frank Caliendo'), 
   ('2015-01-05', 'Rock of the 80''s'), 
   ('2015-01-15', 'Smokey Robinson Pre-Show Wine & Smoked Cheese Tasting'), 
   ('2015-01-16', 'Smokey Robinson'), 
   ('2015-01-22', 'Rewind Fest 2015:  Psychedelic Furs and More'), 

   ('2015-02-04', 'ZZ Top with Special Guest Blackberry Smoke'), 
   ('2015-02-07', 'Block Party With Kool & The Gang And More'), 
   ('2015-02-13', 'Thunder Vibes Reggae Festival'), 
   ('2015-02-14', 'Michael McDonald and Boz Scaggs'), 
   ('2015-02-16', 'Marco Antonio Solis Y Camilia - La Experiencia Tour'), 

   ('2015-02-26', 'Gladiator Challenge: Collision Course'), 
   ('2015-02-27', 'Rick Springfield'), 
   ('2015-03-04', 'The Thunder Down Under Australian Nudie Revue'), 
   ('2015-03-05', 'The Cosplay Five Sings The Hits'), 
   ('2015-03-10', 'Strawberry Music Fest - Amy''s Orchid Late Nite Lounge'), 

   ('2015-03-27', 'Kenny G: The Rock Opera'), 
   ('2015-03-28', 'Purple Ones - Tribute to the Music of Prince'), 
   ('2015-03-31', 'Steel Slinky - Party Band'), 
   ('2015-04-05', 'Chains Required - Horn driven R&B'), 
   ('2015-04-17', 'Cheezy Poofs = A Dash of alt-rock with soul and funk'), 

   ('2015-04-23', 'Rewind Fest 2015:  Psychedelic Furs and More'), 
   ('2015-04-24', 'Shaq''s All-Star Comedy Jam'), 
   ('2015-05-15', 'Peppermint Patty and Mustafa''s All Star Jamacian Steel Drum Band'), 
   ('2015-05-21', 'IFC Caged Combat'), 
   ('2015-06-15', 'The Fabulous Jewish Magician Signumd J. Goldstein'), 

   ('2015-06-30', 'Pearl City Marathon'), 
   ('2015-07-20', 'Tough Mudder'), 
   ('2015-09-05', 'Huey Lewis and The News' ), 
   ('2015-09-18', 'Sarah Colonna and feature act Jeff Bodart'), 
   ('2015-09-19', 'Sarah Colonna and feature act Jeff Bodart'), 

   ('2015-09-12', 'Duc Huy: 50 Years of Love and Music'), 
   ('2015-09-25', 'ABBACADABRA–The Ultimate ABBA Tribute'), 
   ('2015-09-26', 'Last Comic Standing Live Tour'), 
   ('2015-09-09', 'America''s Got Talent Live: The All-Stars Tour!'), 
   ('2015-10-29', 'Flashdance – The Musical'), 

   ('2015-10-10', 'Kenny Rogers' ), 
   ('2015-10-31', 'Sinners & Saints Halloween Party'), 
   ('2015-11-20', 'Donny & Marie Celebrating the Holidays'), 
   ('2015-11-21', 'Donny & Marie Celebrating the Holidays')

-- Set the days with events
UPDATE d
SET d.is_event = 1, d.Description = t.description
FROM #tmp t
   JOIN days d ON t.PKDate = d.PKDate

-- Set the days without events
UPDATE days SET is_event = 0 WHERE is_event IS NULL

Open in new window


sqlsat453.jpg 
39
Author:Jim Horn
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free