<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

SQL Server Calendar Table

Published on
67,511 Points
31,911 Views
36 Endorsements
Last Modified:
Awarded
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 
36
Comment
Author:Jim Horn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
27 Comments
 
LVL 49

Expert Comment

by:PortletPaul
Jim, bound to be very useful reference. Well done.
0
 
LVL 3

Expert Comment

by:hspoulsen
For data warehousing, the use of ID's instead of dates for increased query performance.  Not discussed in this article.

I would like to hear what you can achieve here.
The new DATE datatype is only 3 bytes, and I think IDs would have to be 4 bytes.

We run a lot of queries on tables which are clustered by EquipmentID and EventTime. Datatypes are INT and SmallDateTime respectively.

We have been searching high and low for a good key here, but  so far not found any.
Any ideas?

Best regards,
Henrik
0
 
LVL 37

Expert Comment

by:ValentinoV
Good one Jim, one up!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Expert Comment

by:atljarman
Jim,  how would you use this function to calculate the date x days into the future? Like 30 business days?
0
 

Expert Comment

by:wreckingb123
Found lots of errors in the code, specifically the Thanksgiving days were off.
0
 
LVL 66

Author Comment

by:Jim Horn
If you could post them here, I'd greatly appreciate it.
0
 

Expert Comment

by:HSI_guelph
The code wouldn't run with the Use calendar go code in it.  I took that out but now I am not getting the month names or other stuff (they are coming up null).  I'm running the code in SQL server studio so that could be the issue.  Is there a way to work around this?
0
 

Expert Comment

by:aflcio-hit
To HSI guelph -- there are 2 errors in the code... one is to make the 'description' field bigger -- I changed to 80 just to be safe.. and the second is to change the 'day_name_long_with_suffix'  to 'mdy_name_long_with_suffix' in the update statement... as far as thanksgiving here is a way to correct: (Thanksgiving is already correct, but the Friday after will pick the previous Friday if the Friday falls in week five (if the first day of the month is a Friday)
** I dropped "American" from the code when I ran it... add this back in if needed before you run the update statements.
 
-- Crear all thanksgiving ( this will add Friday's as 'Thanksgiving Friday')
 UPDATE days
SET is_holiday = 0, description = '' -- fourth Thursday in November plus the adjoining Friday
where description = 'Thanksgiving'

UPDATE days
SET is_holiday = 1, description = '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 = 'Thanksgiving Friday' 
from days where PKDate in (
select dateadd(d,1,pkdate)
from days where description = 'Thanksgiving'
)

Open in new window


-- Update Memorial Day

UPDATE days
SET is_holiday = 1, description = 'Memorial Day' -- last Monday in May --select *
from days 
inner 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

Open in new window


Great Stuff!!!! I have created a function to count business days going forward or backward) -- returns a negative if @dtStart is after @dtEnd:  

Also -- if the start date is Wed and the end date if Friday (and all 3 days are work days) -- it will return 2 as that is my functional business case.. Thursday is one working day, Friday is the second working day). If the first day is Saturday and the end date is Monday, it will return one. Customize for your business case.

alter function dbo.f_CountBusinessDays
(
@dtStart as date , 
@dtEnd as date 
)
Returns int
as
begin
--declare @dtStart as date ='2014-12-20' , @dtEnd as date ='2014-12-19'
declare @DayCount int =0
SELECT @DayCount = SUM(is_workday) --as BusDayCount
FROM days
WHERE PKDate BETWEEN @dtStart and @dtEnd

if @DayCount is null
begin
SELECT @DayCount =- SUM(is_workday) --as BusDayCount
FROM days
WHERE PKDate <=@dtStart and PKDate >= @dtEnd
end
--subtract one (ie if the start and end dates are the same -- this will be zero days -not one)
if ((select sum(is_workday) from days where pkdate = @dtStart or pkDate = @dtEnd )=2)
begin -- Only apply this logic if both days passed are business days
	if (@DayCount > 0 ) 
	begin
		set @DayCount = @DayCount -1
	end
	if @DayCount < 0
	begin
		set @DayCount = @DayCount + 1
	end
end
return @DayCount
end

declare @dtStart as date ='2014-12-20' , @dtEnd as date ='2014-12-19'
select dbo.f_CountBusinessDays(@dtStart, @dtEnd)

Open in new window

0
 

Expert Comment

by:Mehawitchi
Great Stuff!! This has become the official calender table for a big DW for a TV station.
Thanks in loads Jim
0
 
LVL 66

Author Comment

by:Jim Horn
Wow that's very flattering, thank you very much, and glad you find it useful.

btw I have a sequel in the works on dealing with fiscal calendars and rolling averages, so your kind words will give me more motivation to publish it.
0
 

Expert Comment

by:Mehawitchi
Please do. I'm sure it would benefit a lot of professionals.

I would also advise you to add more examples on the use of calendar tables in real life queries, because this would add great value to the subject.

Thanks again for the great post!!
0
 
LVL 66

Author Comment

by:Jim Horn
Will do.  I'll let you know when that's published.
0
 

Expert Comment

by:Mehawitchi
Cheers
0
 

Expert Comment

by:cindyfiller
This has been super helpful.  I'm using this to create a calendar in SSRS, but need one more thing.  In May, the first day of the month is a Friday.  In order for the days to print properly on a calendar, I need to include Sun - Thurs of the previous month.  Any ideas on that?
0
 
LVL 66

Author Comment

by:Jim Horn
Cindy - Nothing that immediately comes to mind, but I've sat through a number of SSRS calendar presentations by Brian Larson of Superior Consulting, and if you Google for any of his SSRS presentations and look through the books he's published you may get to an answer.

Good luck.
0
 
LVL 66

Author Comment

by:Jim Horn
Cindy - It's worth asking as an EE question to see if any other expert has a solution.
0
 

Expert Comment

by:cindyfiller
I did find some code he did to address this... now I'm trying to make sense of it so I can incorporate it into your code.  

Thank you!
0
 
LVL 101

Expert Comment

by:mlmcc
What changes are needed to use this with SQL Server 2000?

mlmcc
0
 
LVL 66

Author Comment

by:Jim Horn
I would think none, as SQL2K also has the date data type, but I have no means to test this as I don't have a SQL2K dev box handy.
0
 
LVL 51

Expert Comment

by:Mark Wills
No, SQL 2000 does not have DATE but has DATETIME

Can still be made to work with DATETIME data type. Just need zero out the time component

the dateadd day to datediff day and target date e.g. select dateadd(day,0, datediff(day,0,getdate()))  or just use LHS of datetime to float data type.
0
 

Expert Comment

by:John Pittaway
Jim, this kind of content is why I subscribe to EE.

I had a problem with the Previous Week Boundaries query.

Declare @dt date = '2015-09-23', @dtWeekBegin as date, @dtWeekEnd as date
SELECT MIN(PKDate) StartDate, MAX(PKDate) EndDate
FROM days
WHERE calendar_week_in_year = (SELECT calendar_week_in_year - 1 FROM days WHERE PKDate = @dt)

Open in new window

Returns:
StartDate	EndDate
2010-09-12	2020-09-19

Open in new window


I tightened the WHERE filter:

Declare @dt date = '2015-09-23', @dtWeekBegin as date, @dtWeekEnd as date
SELECT MIN(PKDate) StartDate, MAX(PKDate) EndDate
FROM days
WHERE calendar_week_in_year = (SELECT calendar_week_in_year - 1 FROM days WHERE PKDate = @dt)
AND YEAR(PKDate) = YEAR(@dt)  -- ADDED

Open in new window


Result:
StartDate	EndDate
2015-09-13	2015-09-19

Open in new window

0
 

Expert Comment

by:John Pittaway
Found another small problem.  The declaration for the days.continuous_quarter column is missing in the "entire code block" at the end of the article.
0
 

Expert Comment

by:John Pittaway
BTW, when and where will you explain the use of continuous time spans???
0
 
LVL 7

Expert Comment

by:Yashwant Vishwakarma
Wow :) , Congratulation Jim that your article is selected for SQL PASS Saturday.

Have a great day ahead :)
0
 

Expert Comment

by:Sherry
Thank Jim.  Great resource for me to follow. :)
0
 

Expert Comment

by:Becky Edwards
Good Article Jim.  Thanks for all the help!
0
 

Expert Comment

by:Jag Singh
Hi Jim,

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


Regards,
Jag
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Join & Write a Comment

Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month