Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

SQL Server Calendar Table

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.
Published:
Updated:
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 
43
47,320 Views
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.

Comments (29)

SherryDeveloper

Commented:
Thank Jim.  Great resource for me to follow. :)
Becky EdwardsEpic Clarity Developer

Commented:
Good Article Jim.  Thanks for all the help!
Hi Jim,

Thanks for the script. What if we want to show the First Day of Week i.e Monday's date.


Regards,
Jag

Commented:
Awesome!!!
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Author

Commented:
Hey Miriam - the flag icon next to your name says Kazakhstan, which is a ways away from Maryland.  Something you want to tell us? 

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.