Here's the promised code. On my db server, this code lives in a
Reference_Tables
database. Change the
USE
statement to reflect your own paradigm.
First, is the IsHoliday scalar function. If you don't need federal holidays, you can skip this, but if you do need this, you must add this function *before* adding the stored proc below.
USE [Reference_Tables]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_IsHoliday]
(
@date date
)
RETURNS bit
AS
BEGIN
DECLARE @year int = DATEPART(YEAR, @date);
DECLARE @month int = DATEPART(MONTH,@date);
DECLARE @day int = DATEPART(DAY, @date);
DECLARE @dayName varchar(12) = DATENAME(DW, @date );
DECLARE @nthWeekDay int = ceiling(@day / 7.0);
DECLARE @isThursday bit = CASE WHEN @dayName LIKE 'Thursday' THEN 1 ELSE 0 END;
DECLARE @isFriday bit = CASE WHEN @dayName LIKE 'Friday' THEN 1 ELSE 0 END;
DECLARE @isSaturday bit = CASE WHEN @dayName LIKE 'Saturday' THEN 1 ELSE 0 END;
DECLARE @isSunday bit = CASE WHEN @dayName LIKE 'Sunday' THEN 1 ELSE 0 END;
DECLARE @isMonday bit = CASE WHEN @dayName LIKE 'Monday' THEN 1 ELSE 0 END;
DECLARE @isWeekend bit = CASE WHEN @isSaturday = 1 OR @isSunday = 1 THEN 1 ELSE 0 END;
if (@month = 12 AND @day = 31 AND @isFriday=1) return 1;
if (@month = 1 AND @day = 1 AND @isWeekend=0) return 1;
if (@month = 1 AND @day = 2 AND @isMonday=1) return 1;
if (@month = 1 AND @isMonday = 1 AND @nthWeekDay = 3) return 1;
if (@month = 2 AND @isMonday = 1 AND @nthWeekDay = 3) return 1;
if (@month = 5 AND @isMonday = 1 AND DATEPART(MONTH, DATEADD(DAY, 7, @Date)) = 6) return 1;
if (@month = 7 AND @day = 3 AND @isFriday = 1) return 1;
if (@month = 7 AND @day = 4 AND @isWeekend = 0) return 1;
if (@month = 7 AND @day = 5 AND @isMonday = 1) return 1;
if (@month = 9 AND @isMonday = 1 AND @nthWeekDay = 1) return 1;
if (@month = 10 AND @isMonday = 1 AND @nthWeekDay = 2) return 1;
if (@month = 11 AND @day = 10 AND @isFriday = 1) return 1;
if (@month = 11 AND @day = 11 AND @isWeekend = 0) return 1;
if (@month = 11 AND @day = 12 AND @isMonday = 1) return 1;
if (@month = 11 AND @isThursday = 1 AND @nthWeekDay = 4) return 1;
if (@month = 12 AND @day = 24 AND @isFriday = 1) return 1;
if (@month = 12 AND @day = 25 AND @isWeekend = 0) return 1;
if (@month = 12 AND @day = 25 AND @isMonday = 1) return 1;
return 0;
END
GO
This stored procedure returns a table of dates based on the specified date range (the date range can be a single day if desired).
USE [Reference_Tables]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_GetCalendar]
@startDate DATE,
@endDate DATE
AS
BEGIN
SET NOCOUNT ON;
if (@startDate > @endDate)
BEGIN
DECLARE @temp date = @startDate;
SET @startDate = @endDate;
SET @endDate = @temp;
END
;WITH n AS
(
SELECT TOP (DATEDIFF(DAY, @startDate, @endDate) + 1) n = ROW_NUMBER() OVER (ORDER BY [object_id])
FROM sys.all_objects
)
SELECT DATEADD(DAY, n-1, @startDate) AS CalendarDate
,DATEPART(YEAR, DATEADD(DAY, n-1, @startDate)) AS CalendarYear
,DATEPART(MONTH, DATEADD(DAY, n-1, @startDate)) AS CalendarMonth
,DATEPART(QUARTER, DATEADD(DAY, n-1, @startDate)) AS CalendarQuarter
,DATEPART(DAYOFYEAR, DATEADD(DAY, n-1, @startDate)) As CalendarJulianDay
,DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate)) AS FiscalDate
,DATEPART(YEAR, DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate))) AS FiscalYear
,DATEPART(MONTH, DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate))) AS FiscalMonth
,DATEPART(QUARTER, DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate))) AS FiscalQuarter
,DATEPART(DAYOFYEAR, DATEADD(MONTH, 3, DATEADD(DAY, n-1, @startDate))) As FiscalJulianDay
,DATENAME(MONTH, DATEADD(DAY, n-1, @startDate)) AS [MonthName]
,DATEPART(DAY, DATEADD(DAY, n-1, @startDate)) AS [DayOfMonth]
,DATEPART(WEEKDAY, DATEADD(DAY, n-1, @startDate)) AS WeekDayNumber
,DATENAME(WEEKDAY, DATEADD(DAY, n-1, @startDate)) AS WeekDayName
,ceiling(DATEPART(DAY, DATEADD(DAY, n-1, @startDate)) / 7.0) As NthWeekday
,dbo.fn_IsHoliday(DATEADD(DAY, n-1, @startDate)) AS IsHoliday
FROM n;
END
GO
To get the nth week (in a month) in which a date falls, you can use the returned
NthWeekday
column.