Saving Dates
A long time ago in a job far far away, I needed to find a way to store special dates in SQL. These were for holidays and personal reminders that the users would create themselves. I checked different methods for keeping the date information. Some ideas worked fast and some did not. None of them really fit for what I needed for this system. I decided to build a solution that would let me have a lot of flexibility of recording regular holiday dates and also recording special event dates. Like a special sale on spatulas every 3rd Wednesday of February.
The first step was making a table to hold the special dates. I first focused on holidays as they were variable in their requirements on what day they fell on each year. I wanted this table to be flexible enough to display the holidays and how to calculate them correctly. I also wanted the users to make their own special dates (spatula sale) that could repeat on their own schedule. If they change the schedule, then this needed to be shown on the calendar and have the table be able to show this change.
Holiday Data Table
This table records the details on how to recreate a specific holiday or special date. It is not the actual date for a given year. Holidays can be based on a specific day (Christmas - Dec 25). They can be based on a day of week (Columbus Day - 2nd Monday of October). They can be based on other holidays (Mardi Gras - 47 days before Easter). They can even be based on a special calculation of the lunar phase (Easter). This table allows you to use all of these options for setting a date. This table also includes what years a holiday is active. For example, some special holidays, such as Daylight Saving Time, have shifted days each year and other holidays, such as Patriot Day (September 11, 2001), were not celebrated before a certain date. The fields I use in the holiday data table are:
| SysID | Identity field for the table. This lets the table have a unique key separate from the holiday key. |
| HolidayID | Numeric identifier for a specific Holiday record (Unique). |
| Title | Short name identifying what this holiday is. |
| ReportFlag | Should this holiday be reported (Turning a holiday on or off). |
| DayOffFlag | Does this holiday give a day off. |
| StartYear | What year did this holiday start. |
| EndYear | What year did this holiday end. |
| DaysPrior | How many days prior, as a positive number, to the date recorded in this record does this holiday start (multi-day holidays). |
| DaysAfter | How many days after, as a positive number, the date recorded in this record does this holiday end (multi-day holidays) (ex: Spatual Sale - 2 days only). |
| MonthOf | What month does this holiday occur (0 (None) - 12 (Dec)). |
| DayOf | What day does this holiday occur (0 - 31). |
| NameDay | What specific day of week, as a number, does this holiday occur (0 (None) - 7 (sat)). |
| WeekNumber | What specific week within a month does this holiday occur (0 - 6). |
| LastDayFlag | Does the holiday happen on the last day of a specified time period (ex: Memorial Day - Last Monday of May). |
| FullWeekFlag | Does the holiday use full weeks (Sun - Sat) to determine the date (ex: Teachers Day - First Tues of full week in May). |
| BaseHoliday | What holiday is this holiday based on for its holiday date (The base holidays HolidayID). |
| DaysVariance | How many days removed is this holiday from the base date (ex: Mardi Gras -47 days from Easter). |
| SpecialCalc | Does the holiday use a special calculation to find the correct date (ex: Easter). |
| SlideDate | Does the holiday "Slide" to a different week day (mon, fri) when on the weekend (0 (No) - 3 (To Monday)) (ex: Christmas observed on Fri. if it falls on a Sat.). |
Code for creating the Holidays table:
IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[Holidays]') AND [type]='U'))
DROP TABLE [dbo].[Holidays]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Holidays] (
[SysID] [int] IDENTITY(1, 1) NOT NULL,
[HolidayID] [int] NOT NULL DEFAULT ((0)),
[Title] [varchar](100) NOT NULL DEFAULT '',
[ReportFlag] [bit] NOT NULL DEFAULT ((0)),
[DayOffFlag] [bit] NOT NULL DEFAULT ((0)),
[StartYear] [int] NOT NULL DEFAULT ((0)),
[EndYear] [int] NOT NULL DEFAULT ((0)),
[DaysPrior] [int] NOT NULL DEFAULT ((0)),
[DaysAfter] [int] NOT NULL DEFAULT ((0)),
[MonthOf] [int] NOT NULL DEFAULT ((0)),
[DayOf] [int] NOT NULL DEFAULT ((0)),
[NameDay] [int] NOT NULL DEFAULT ((0)),
[WeekNumber] [int] NOT NULL DEFAULT ((0)),
[LastDayFlag] [bit] NOT NULL DEFAULT ((0)),
[FullWeekFlag] [bit] NOT NULL DEFAULT ((0)),
[BaseHoliday] [int] NOT NULL DEFAULT ((0)),
[DaysVariance] [int] NOT NULL DEFAULT ((0)),
[SpecialCalc] [int] NOT NULL DEFAULT ((0)),
[SlideDate] [int] NOT NULL DEFAULT ((0))
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [Idx_HolidayID]
ON [dbo].[Holidays] ([HolidayID])
ON [PRIMARY]
GO
Calculating The Holiday
The records from the Holiday table are passed into a function called fn_FindDate() to calculate the actual date of the holiday. The fn_FindDate() function was designed to be the primary function for the records in the Holiday table.
fn_FindDate():
IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_FindDate]') AND ([type]='IF' OR [type]='FN' OR [type]='TF')))
DROP FUNCTION [dbo].[fn_FindDate]
GO SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO CREATE FUNCTION [dbo].[fn_FindDate]
(
@HolidayIDIn int,
@TitleIn varchar(100),
@ReportFlagIn bit,
@DayOffFlagIn bit,
@StartYearIn int,
@EndYearIn int,
@DaysPriorIn int,
@DaysAfterIn int,
@MonthOfIn int,
@DayOfIn int,
@NameDayIn int,
@WeekNumberIn int,
@LastDayFlagIn bit,
@FullWeekFlagIn bit,
@BaseHolidayIn int,
@DaysVarianceIn int,
@SpecialCalcIn int,
@YearIn int,
@SlideDateIn int
)
/*
Any fields not used in calculating a date can be defaulted to 0 (Not used).
@HolidayIDIn - Unique ID for a specific holiday.
@TitleIn - Title of a holiday.
@ReportFlagIn - Should this be reported (0, 1).
@DayOffFlagIn - Day off from work flag (0, 1).
@StartYearIn - Year the holiday started.
@EndYearIn - Year the holiday finished.
@DaysPriorIn - Days prior to the date recorded the holiday starts.
@DaysAfterIn - Days after to the date recorded the holiday continues.
@MonthOfIn - Month the holiday is in.
@DayOfIn - Number day the holiday starts.
@NameDayIn - Name day (Sun - Sat) as a number the holiday starts.
@WeekNumberIn - Week of the month the holiday starts.
@LastDayFlagIn - Does the holiday start on the last day of a specific name day.
@FullWeekFlagIn - Does the holiday use full weeks only to determine the start day.
@BaseHolidayIn - What holiday is used as the basis for this holiday.
@SpecialCalcIn - Which special calculation is used to find the holiday date.
@YearIn - Year to check to determine the holiday date.
@SlideDateIn - Does this holiday change the reported date if it falls in the weekend.
*/
RETURNS datetime
AS
BEGIN
DECLARE @DateType int
DECLARE @DateOut datetime
DECLARE @DateCheck datetime
DECLARE @HoldID int
DECLARE @NewMonth int
DECLARE @NumYears int
DECLARE @DayOfWeek int
IF EXISTS(SELECT 1 FROM HolidayDates WHERE HolidayID = @HolidayIDIn AND Year = @YearIn)
BEGIN
/*
Retrieve date from the HolidayDates table. Much faster than recalculating.
*/
SELECT
@DateOut = DayStart
FROM HolidayDates
WHERE
HolidayID = @HolidayIDIn
AND Year = @YearIn
END
ELSE
BEGIN
/*
Find what type (DateType) of holiday this is.
0 - Not Set.
1 - Specific Number Day Of Month.
2 - Specified By Name Day Of Month Or Specific Week Of Month.
3 - Uses A Different Holiday As Its Base.
4 - Uses A Special Calculation (Complex Calculation).
*/
SELECT
@DateType = 0,
@DateOut = NULL
IF @DayOfIn > 0
BEGIN
SELECT
@DateType = 1
END
IF @NameDayIn > 0 OR @WeekNumberIn > 0
BEGIN
SELECT
@DateType = 2
END
IF @BaseHolidayIn > 0
BEGIN
SELECT
@DateType = 3
END
IF @SpecialCalcIn > 0
BEGIN
SELECT
@DateType = 4
END
IF @DateType = 1
BEGIN
/*
Calculate the specific date based on the Year, Month, and Day passed in.
Adjust date for Year and Month wrapping.
*/
IF @MonthOfIn > 12
BEGIN
/*
Find the last day of the month for the specified date as months will vary on the number of days (28 - 31).
If the last day of the month for the specified month is less than the specified day, then use the
last day of the month as the specified day otherwise calculate the specified day.
*/
SELECT
@NumYears = CAST((@MonthOfIn / 12) AS int),
@NewMonth = @MonthOfIn % 12
SELECT
@DateCheck =
DATEADD
(
dd,
-1,
CAST
(
CAST((@YearIn + @NumYears) AS char(4)) +
'-' +
CAST((@NewMonth + 1) AS char(2)) +
'-01'
AS datetime
)
)
IF DATEPART(dd, @DateCheck) < @DayOfIn
BEGIN
SELECT
@DateOut = @DateCheck
END
ELSE
BEGIN
SELECT
@DateOut =
CAST
(
CAST((@YearIn + @NumYears) AS char(4)) +
'-' +
CAST(@NewMonth AS char(2)) +
'-' +
CAST(@DayOfIn AS char(2))
AS datetime
)
END
END
ELSE
BEGIN
/*
Find the last day of the month for the specified date as months will vary on the number of days (28 - 31).
If the last day of the month for the specified month is less than the specified day, then use the
last day of the month as the specified day otherwise calculate the specified day.
*/
IF @MonthOfIn < 12
BEGIN
SELECT
@DateCheck =
DATEADD
(
dd,
-1,
CAST
(
CAST((@YearIn) AS char(4)) +
'-' +
CAST((@MonthOfIn + 1) AS char(2))
+ '-01'
AS datetime
)
)
END
ELSE
BEGIN
SELECT
@DateCheck = CAST(CAST((@YearIn) AS char(4)) + '-12-31' AS datetime)
END
IF DATEPART(dd, @DateCheck) < @DayOfIn
BEGIN
SELECT
@DateOut = @DateCheck
END
ELSE
BEGIN
SELECT
@DateOut =
CAST
(
CAST(@YearIn AS char(4)) +
'-' +
CAST(@MonthOfIn AS char(2)) +
'-' +
CAST(@DayOfIn AS char(2))
AS datetime
)
END
END
END
IF @DateType = 2
BEGIN
/*
Uses a specific name day or week of month for the date.
If the LastDayFlag is on, then need to find the final occurrence of the name day
or the last week of the month. Both of these use a function to find the specific
number day of the month.
*/
IF @LastDayFlagIn = 1
BEGIN
SELECT
@DateOut =
CAST
(
CAST(@YearIn AS char(4)) +
'-' +
CAST(@MonthOfIn AS char(2)) +
'-' +
CAST
(
dbo.fn_LastDay
(
@YearIn,
@MonthOfIn,
@NameDayIn - 1,
@FullWeekFlagIn
)
AS char(2)
)
AS datetime
)
END
ELSE
BEGIN
SELECT
@DateOut =
CAST
(
CAST(@YearIn AS char(4)) +
'-' +
CAST(@MonthOfIn AS char(2)) +
'-' +
CAST
(
dbo.fn_WhatDate
(
@YearIn,
@MonthOfIn,
@NameDayIn - 1,
@WeekNumberIn,
@FullWeekFlagIn
)
AS char(2)
)
AS datetime
)
END
END
IF @DateType = 3
BEGIN
/*
A different holiday is used as the basis for this date. This date will be a variance (Number
of days offset) from the base holiday.
*/
SELECT
@DateOut =
dbo.fn_FindDate
(
HolidayID,
Title,
ReportFlag,
DayOffFlag,
StartYear,
EndYear,
DaysPrior,
DaysAfter,
MonthOf,
DayOf,
NameDay,
WeekNumber,
LastDayFlag,
FullWeekFlag,
BaseHoliday,
DaysVariance,
SpecialCalc,
@YearIn,
SlideDate
) +
@DaysVarianceIn
FROM Holidays
WHERE
HolidayID = @BaseHolidayIn
END
IF @DateType = 4
BEGIN
/*
A special complex calculation is used to find the date.
*/
IF @SpecialCalcIn = 1 -- Get Easter Day
BEGIN
/*
Easter uses a function with the calculations to find the correct date for a specific year.
*/
SELECT
@DateOut = dbo.fn_GetEaster(@YearIn)
END
IF @SpecialCalcIn = 2 -- Get Election Day
BEGIN
/*
Election Day happens once every 4 years on the first Tuesday after the first Monday.
If fn_WhatDate() returns (1), then add 7 because that means November started on Tuesday
and we need to be after the first Monday.
*/
SELECT
@DayOfWeek = dbo.fn_WhatDate(CAST(((@YearIn + 3) / 4) AS int) * 4, 11, 2, 1, 0)
IF @DayOfWeek = 1
BEGIN
SELECT
@DateOut =
CAST
(
CAST
(
CAST(((@YearIn + 3) / 4) AS int) * 4
AS char(4)
) +
'-11-' +
CAST((@DayOfWeek + 7) AS char(2))
AS datetime
)
END
ELSE
BEGIN
SELECT
@DateOut =
CAST
(
CAST
(
CAST(((@YearIn + 3) / 4) AS int) * 4
AS char(4)
) +
'-11-' +
CAST((@DayOfWeek + 7) AS char(2))
AS datetime
)
END
END
IF @SpecialCalcIn = 3 -- Get Federal Income Taxes, Due on 4-15 or Monday(3rd) after if it falls on a weekend.
BEGIN
/*
Income taxes are due on the same day every year except when it falls on a weekend.
On a weekend, the income taxes are due on the following Monday.
This special calculation is now obsolete with the addition of the SlideDate field. Now a regular
Holiday record can be created for April 15 with a SlideDate set to 3 (Following Monday If Weekend).
*/
SELECT
@DateOut = CAST(CAST(@YearIn AS char(4)) + '-04-15' AS datetime)
IF DATEPART(dw, @DateOut) = 1 OR DATEPART(dw, @DateOut) = 7
BEGIN
SELECT
@DateOut =
CAST
(
CAST(@YearIn AS char(4)) +
'-04-' +
CAST(dbo.fn_WhatDate(@YearIn, 4, 1, 3, 0) AS char(2))
AS datetime
)
END
END
IF @SpecialCalcIn = 4 -- Get Inauguration Day (on 1-20 once every 4 years)
BEGIN
/*
Presidents are inaugurated only once every 4 years (4 year term).
*/
SELECT
@DateOut =
CAST
(
CAST
(
(
CAST(((@YearIn + 2) / 4) AS int)
* 4 + 1
)
AS char(4)
) +
'-01-20'
AS datetime
)
END
END
IF @SlideDateIn > 0
BEGIN
/*
Some special dates need to be adjusted if they fall on a weekend. For this, there are
3 different ways to adjust the date.
1 - Shift to the closest weekend (Sat = Fri, Sun = Mon).
2 - Shift to Friday (Sat, Sun = Fri).
3 - Shift to Monday (Sat, Sun = Mon).
*/
SELECT
@DayOfWeek = DATEPART(dw, @DateOut)
IF @DayOfWeek = 1 OR @DayOfWeek = 7
BEGIN
SELECT
@DateOut =
(
CASE @SlideDateIn
WHEN 1 THEN -- Shift To Closest Weekend
(
CASE @DayOfWeek
WHEN 1 THEN -- Sunday
DATEADD(dd, 1, @DateOut)
WHEN 7 THEN -- Saturday
DATEADD(dd, -1, @DateOut)
END
)
WHEN 2 THEN -- Shift To Friday
(
CASE @DayOfWeek
WHEN 1 THEN -- Sunday
DATEADD(dd, -2, @DateOut)
WHEN 7 THEN -- Saturday
DATEADD(dd, -1, @DateOut)
END
)
WHEN 3 THEN -- Shift To Monday
(
CASE @DayOfWeek
WHEN 1 THEN -- Sunday
DATEADD(dd, 1, @DateOut)
WHEN 7 THEN -- Saturday
DATEADD(dd, 2, @DateOut)
END
)
END
)
END
END
END
/*
Return the calculated date, with any adjustments, for the special day of the specified year.
*/
RETURN(@DateOut)
END
GO
The fn_FindDate function has a lot of parameters but most of them are not used at any one time. The unused fields would be defaulted to 0. for example, to find the date for April Fools Day (April 1st) for 2010:
| TitleIn | 'April Fools Day' |
| MonthOfIn | 4 (April) |
| DayOfIn | 1 |
| YearIn | 2010 |
All other fields can be left at the default of 0. The code would look like:
SELECT
dbo.fn_FindDate
(
0, -- @HolidayIDIn - Unique ID for a specific holiday.
'April Fools Day', -- @TitleIn - Title of a holiday.
0, -- @ReportFlagIn - Should this be reported (0, 1).
0, -- @DayOffFlagIn - Day off from work flag (0, 1).
0, -- @StartYearIn - Year the holiday started.
0, -- @EndYearIn - Year the holiday finished.
0, -- @DaysPriorIn - Days prior to the date recorded the holiday starts.
0, -- @DaysAfterIn - Days after to the date recorded the holiday continues.
4, -- @MonthOfIn - Month the holiday is in.
1, -- @DayOfIn - Number day the holiday starts.
0, -- @NameDayIn - Name day (Sun - Sat) as a number the holiday starts.
0, -- @WeekNumberIn - Week of the month the holiday starts.
0, -- @LastDayFlagIn - Does the holiday start on the last day of a specific name day.
0, -- @FullWeekFlagIn - Does the holiday use full weeks only to determine the start day.
0, -- @BaseHolidayIn - What holiday is used as the basis for this holiday.
0, -- @DaysVarianceIn - Number of days holiday is offset from the base holiday.
0, -- @SpecialCalcIn - Which special calculation is used to find the holiday date.
2010, -- @YearIn - Year to check to determine the holiday date.
0 -- @SlideDateIn - Does this holiday change the reported date if it falls in the weekend.
) AS HolidayDate
Technically, the TitleIn does not need to be filled out for this call and could be left blank but it does add to the readability. We can find a date that is based on a specific name day of the month, for example, finding the date for Labor Day (First Monday in September):
| TitleIn | 'Labor Day' |
| MonthOfIn | 9 (September) |
| NameDayIn | 2 (Monday) |
| WeekNumberIn | 1 (First Monday of the month) |
| YearIn | 2010 |
The rest of the fields can be left at the default of 0. The code would look like:
SELECT
dbo.fn_FindDate
(
0, -- @HolidayIDIn - Unique ID for a specific holiday.
'Labor Day', -- @TitleIn - Title of a holiday.
0, -- @ReportFlagIn - Should this be reported (0, 1).
0, -- @DayOffFlagIn - Day off from work flag (0, 1).
0, -- @StartYearIn - Year the holiday started.
0, -- @EndYearIn - Year the holiday finished.
0, -- @DaysPriorIn - Days prior to the date recorded the holiday starts.
0, -- @DaysAfterIn - Days after to the date recorded the holiday continues.
9, -- @MonthOfIn - Month the holiday is in.
0, -- @DayOfIn - Number day the holiday starts.
2, -- @NameDayIn - Name day (Sun - Sat) as a number the holiday starts.
1, -- @WeekNumberIn - Week of the month the holiday starts.
0, -- @LastDayFlagIn - Does the holiday start on the last day of a specific name day.
0, -- @FullWeekFlagIn - Does the holiday use full weeks only to determine the start day.
0, -- @BaseHolidayIn - What holiday is used as the basis for this holiday.
0, -- @DaysVarianceIn - Number of days holiday is offset from the base holiday.
0, -- @SpecialCalcIn - Which special calculation is used to find the holiday date.
2010, -- @YearIn - Year to check to determine the holiday date.
0 -- @SlideDateIn - Does this holiday change the reported date if it falls in the weekend.
) AS HolidayDate
The National Teachers Day holiday is the first Tuesday in May in a full week (day in month starts on Sunday). This would be similar to the above example with the inclusion of the LastDayFlag:
| TitleIn | 'National Teachers Day' |
| MonthOfIn | 5 (May) |
| NameDayIn | 3 (Tuesday) |
| WeekNumberIn | 1 (First Week) |
| FullWeekFlagIn | 1 (Week in month starts on Sunday) |
| YearIn | 2010 |
The rest of the fields can be left at the default of 0. The code would look like:
SELECT
dbo.fn_FindDate
(
0, -- @HolidayIDIn - Unique ID for a specific holiday.
'National Teachers Day', -- @TitleIn - Title of a holiday.
0, -- @ReportFlagIn - Should this be reported (0, 1).
0, -- @DayOffFlagIn - Day off from work flag (0, 1).
0, -- @StartYearIn - Year the holiday started.
0, -- @EndYearIn - Year the holiday finished.
0, -- @DaysPriorIn - Days prior to the date recorded the holiday starts.
0, -- @DaysAfterIn - Days after to the date recorded the holiday continues.
5, -- @MonthOfIn - Month the holiday is in.
0, -- @DayOfIn - Number day the holiday starts.
3, -- @NameDayIn - Name day (Sun - Sat) as a number the holiday starts.
1, -- @WeekNumberIn - Week of the month the holiday starts.
0, -- @LastDayFlagIn - Does the holiday start on the last day of a specific name day.
1, -- @FullWeekFlagIn - Does the holiday use full weeks only to determine the start day.
0, -- @BaseHolidayIn - What holiday is used as the basis for this holiday.
0, -- @DaysVarianceIn - Number of days holiday is offset from the base holiday.
0, -- @SpecialCalcIn - Which special calculation is used to find the holiday date.
2010, -- @YearIn - Year to check to determine the holiday date.
0 -- @SlideDateIn - Does this holiday change the reported date if it falls in the weekend.
) AS HolidayDate
Finding the dates of holidays that are based on other holidays will require having the Holidays table populated with the base holidays record and knowing what the base holidays HolidayID is. For example, there are at least 5 holidays that are based on the Easter holiday. These are:
| Fat Tuesday (Mardi Gras) | 47 days before Easter |
| Ash Wednesday | 46 days before Easter |
| Palm Sunday | 7 days before Easter |
| Good Friday | 2 days before Easter |
| Ascension Day | 39 days after Easter |
These all require having the Easter holiday recorded in the Holiday table. Easter itself is based on a special calculation and not a regular month/day calculation. The fn_FindDate function has several special calculations already programmed into it:
| SpecialCalcID | Special Calc Description |
| 1 | Easter Day |
| 2 | Election Day |
| 3 | Federal Income Tax Due |
| 4 | Inauguration Day |
This makes calling for the date for Easter easier. The fields needed to call fn_FindDate are:
| TitleIn | 'Easter' |
| SpecialCalcIn | 1 (Special calculation for Easter) |
| YearIn | 2010 |
That is it. The code would like like this:
SELECT
dbo.fn_FindDate
(
0, -- @HolidayIDIn - Unique ID for a specific holiday.
'Easter', -- @TitleIn - Title of a holiday.
0, -- @ReportFlagIn - Should this be reported (0, 1).
0, -- @DayOffFlagIn - Day off from work flag (0, 1).
0, -- @StartYearIn - Year the holiday started.
0, -- @EndYearIn - Year the holiday finished.
0, -- @DaysPriorIn - Days prior to the date recorded the holiday starts.
0, -- @DaysAfterIn - Days after to the date recorded the holiday continues.
0, -- @MonthOfIn - Month the holiday is in.
0, -- @DayOfIn - Number day the holiday starts.
0, -- @NameDayIn - Name day (Sun - Sat) as a number the holiday starts.
0, -- @WeekNumberIn - Week of the month the holiday starts.
0, -- @LastDayFlagIn - Does the holiday start on the last day of a specific name day.
0, -- @FullWeekFlagIn - Does the holiday use full weeks only to determine the start day.
0, -- @BaseHolidayIn - What holiday is used as the basis for this holiday.
0, -- @DaysVarianceIn - Number of days holiday is offset from the base holiday.
1, -- @SpecialCalcIn - Which special calculation is used to find the holiday date.
2010, -- @YearIn - Year to check to determine the holiday date.
0 -- @SlideDateIn - Does this holiday change the reported date if it falls in the weekend.
) AS HolidayDate
Adding the Easter holiday to the Holidays table is straightforward. Most fields that are not used can be left at the default value of 0. The only other fields to include would be the HolidayID (Unique identifier) and ReportFlag (Turns holiday record on or off). The following code can be used to add the Easter holiday if it is not already in the Holiday table.
INSERT INTO Holidays
(
HolidayID,
Title,
ReportFlag,
DayOffFlag,
StartYear,
EndYear,
DaysPrior,
DaysAfter,
MonthOf,
DayOf,
NameDay,
WeekNumber,
LastDayFlag,
FullWeekFlag,
BaseHoliday,
DaysVariance,
SpecialCalc,
SlideDate
)
SELECT
23 AS HolidayID,
'Easter Day' AS Title,
1 AS ReportFlag,
0 AS DayOffFlag,
0 AS StartYear,
0 AS EndYear,
0 AS DaysPrior,
0 AS DaysAfter,
0 AS MonthOf, 0 AS DayOf,
0 AS NameDay,
0 AS WeekNumber,
0 AS LastDayFlag,
0 AS FullWeekFlag,
0 AS BaseHoliday,
0 AS DaysVariance,
1 AS SpecialCalc,
0 AS SlideDate
Now the other holidays can be called fn_FindDate using the following fields:
| TitleIn | 'Fat Tuesday (Mardi Gras)' |
| BaseHolidayIn | 23 (HolidayID for Easter) |
| DaysVarianceIn | -47 |
| YearIn | 2010 |
The rest of the fields can be left at the default of 0. The code for Fat Tuesday would look like this:
SELECT
dbo.fn_FindDate
(
0, -- @HolidayIDIn - Unique ID for a specific holiday.
'Fat Tuesday (Mardi Gras)', -- @TitleIn - Title of a holiday.
0, -- @ReportFlagIn - Should this be reported (0, 1).
0, -- @DayOffFlagIn - Day off from work flag (0, 1).
0, -- @StartYearIn - Year the holiday started.
0, -- @EndYearIn - Year the holiday finished.
0, -- @DaysPriorIn - Days prior to the date recorded the holiday starts.
0, -- @DaysAfterIn - Days after to the date recorded the holiday continues.
0, -- @MonthOfIn - Month the holiday is in.
0, -- @DayOfIn - Number day the holiday starts.
0, -- @NameDayIn - Name day (Sun - Sat) as a number the holiday starts.
0, -- @WeekNumberIn - Week of the month the holiday starts.
0, -- @LastDayFlagIn - Does the holiday start on the last day of a specific name day.
0, -- @FullWeekFlagIn - Does the holiday use full weeks only to determine the start day.
23, -- @BaseHolidayIn - What holiday is used as the basis for this holiday.
-47, -- @DaysVarianceIn - Number of days holiday is offset from the base holiday.
1, -- @SpecialCalcIn - Which special calculation is used to find the holiday date.
2010, -- @YearIn - Year to check to determine the holiday date.
0 -- @SlideDateIn - Does this holiday change the reported date if it falls in the weekend.
) AS HolidayDate
The coding for the others is simple enough. Just exchange the TitleIn and the DaysVarianceIn with the appropriate values for the other holidays based on Easter.
| TitleIn | 'Ash Wednesday' | 'Palm Sunday' | 'Good Friday' | 'Ascension Day' |
| DaysVarianceIn | -46 | -7 | -2 | 39 |
The potential of having holidays based on other holidays can become very handy. Consider if you have the following special days set up for your store. Sale on Cast Iron Skillets. Sale on Oven Mitts - 7 days later. Sale on Spatulas - 7 days later. Sale on knives - 7 days later. Sale on Band-Aids - 7 days later. The Band-Aids record can be based on the Knives record. The Knives record can be based on the Spatulas record. The Spatulas record can be based on the Oven Mitts record. The Oven Mitts record can be based on the Skillets record. The Skillets record is the only one that would need a starting date defined. The fn_FindDate function is recursive and will continue reading records until it gets to the original base record (Skillets) and then will calculate each date back up the chain until the original date request (Band-Aids) has been calculated and returned.
Supporting Functions
The fn_FindDate() function might call any one of three different other supporting functions to help find the correct date. These functions would not normally be called by themselves.
| fn_LastDay() | Finds the last day of a given name day within a month. Includes an optional check for if it's based on full weeks (week must end on a Saturday). |
| fn_WhatDate() | Calculates the date of a holiday that uses a set month and name day of week. Also checks based on a specific week number and full weeks (week must begin on a Sunday). |
| fn_GetEaster() | Calculates the day of Easter for a given year. |
Code for fn_LastDay:
IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_LastDay]') AND ([type]='IF' OR [type]='FN' OR [type]='TF')))
DROP FUNCTION [dbo].[fn_LastDay]
GO SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_LastDay]
(
@YearIn int,
@MonthIn int,
@DayOfHoliday int,
@FullWeekFlag bit
)
/*
@YearIn - Year Of Holiday Check
@MonthIn - Month Of Holiday Check
@DayOfHoliday - Day Of Holiday (0 to 6) Sun to Sat
@FullWeekFlag - Based on full week (Starts counting with first Sunday)
ex: Last Monday in may = LastDay(2004, 5, 1, 0)
*/
RETURNS int
AS
BEGIN
DECLARE @ReturnDay int
DECLARE @LastDayOfMonth datetime
DECLARE @LastDayOfWeek int --(0 - 6) Sun to Sat
DECLARE @LastDayDifference int
/*
Calclulate the last day of the month based on the Year, Month, and Day passed in.
Adjust date for Year and Month wrapping.
*/
IF @MonthIn + 1 > 12
BEGIN
SELECT
@LastDayOfMonth = DATEADD(dd, -1, CAST(CAST(@YearIn + 1 AS char(4)) + '-01-01' AS datetime))
END
ELSE
BEGIN
SELECT
@LastDayOfMonth = DATEADD(dd, -1, CAST(CAST(@YearIn AS char(4)) + '-' + CAST((@MonthIn + 1) AS char(2)) + '-01' AS datetime))
END
/*
Find the name day of the last day of the month. Convert to a number (0 - 6) for calculations.
*/
SELECT
@LastDayOfWeek = DATEPART(dw, @LastDayOfMonth) - 1
/*
Find the number of days different from the last name day of the month and the name day of the holiday.
*/
SELECT
@LastDayDifference = (((@LastDayOfWeek - @DayOfHoliday) + 7) % 7) * -1
/*
Adjust the number day of the holiday based on the last day of the month - the difference.
*/
SELECT
@ReturnDay = DATEPART(dd, DATEADD(dd, @LastDayDifference, @LastDayOfMonth))
/*
if the FullWeekFlag is set (based on last Saturday in month) then adjust the ReturnDay if needed.
*/
IF
@FullWeekFlag = 1
AND @LastDayOfWeek < 6
AND @LastDayOfWeek >=
(
DATEPART
(
dw,
CAST
(
CAST(@YearIn AS char(4)) +
'-' +
CAST(@MonthIn AS char(2)) +
'-' +
CAST(@ReturnDay AS char(2))
AS datetime
)
) - 1
)
BEGIN
SELECT
@ReturnDay = @ReturnDay - 7
END
/*
Return the number date within the month that the holiday falls on for the specified year.
*/
RETURN(@ReturnDay)
END
GO
Code for fn_WhatDate:
IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_WhatDate]') AND ([type]='IF' OR [type]='FN' OR [type]='TF')))
DROP FUNCTION [dbo].[fn_WhatDate]
GO SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_WhatDate]
(
@YearIn int,
@MonthIn int,
@DayOfHoliday int,
@WeekNumber int,
@FullWeekFlag bit
)
/*
@StartOfMonth - Start Of Month (0 to 6) Sun to Sat
@YearIn - Year Of Holiday Check
@MonthIn - Month Of Holiday Check
@DayOfHoliday - Day Of Holiday (0 to 6) Sun to Sat
@WeekNumber - Week Number (1 to 5)
@FullWeekFlag - Based on full week
ex: 3rd fri of April 2004 = WhatDate(2004, 4, 5, 3, False)
*/
RETURNS int
AS
BEGIN
DECLARE @ReturnDay int
DECLARE @StartOfMonth int
DECLARE @DayOfWeek int
/*
Find the name day of the first day of the month and year specified.
*/
SELECT
@StartOfMonth = DATEPART(dw, CAST(CAST(@YearIn AS char(4)) + '-' + CAST(@MonthIn AS char(2)) + '-01' AS datetime)) - 1
/*
Figure the DayOfWeek difference by name day that the holiday falls on compared to the start of the month name day.
Check if the holiday falls on the same name day as the start of the month name day.
Adjust the DayOfWeek if needed.
*/
SELECT
@DayOfWeek = ((7 + (( @DayOfHoliday + 1) - @StartOfMonth)) % 7)
/*
If DayOfWeek is 0 then the holiday is 7 days offset from the start of the month.
ex. month starts on Tuesday and the holiday is on a Monday is 7 day spread.
The 0 needs to be changed to a 7 or else the holiday would be 1 week early.
*/
IF @DayOfWeek = 0
BEGIN
SELECT
@DayOfWeek = 7
END
/*
Calculate the number day based on the week desired and DayOfWeek.
*/
SELECT
@ReturnDay = (@WeekNumber - 1) * 7 + @DayOfWeek
/*
If the FullWeekFlag is set (Start counting from first Monday) then adjust the number day
if necessary.
*/
IF @FullWeekFlag = 1 AND @DayOfWeek < (7 - @StartOfMonth)
BEGIN
SELECT
@ReturnDay = @ReturnDay + 7
END
/*
Return the number day of the holiday for the specified year and month requested.
*/
RETURN(@ReturnDay)
END
GO
Code for fn_GetEaster:
IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_GetEaster]') AND ([type]='IF' OR [type]='FN' OR [type]='TF')))
DROP FUNCTION [dbo].[fn_GetEaster]
GO SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetEaster]
(
@YearIn int
)
/*
Calculate Easter for the specified year. This calculation was based on a GWBasic program I had used
and kept for a very long time. The program was not very detailed for the different parts and their
meanings so I am not able to detail and document this function very well.
*/
RETURNS datetime
AS
BEGIN
DECLARE @ReturnDate datetime
DECLARE @A int
DECLARE @B int
DECLARE @C int
DECLARE @D int
DECLARE @E int
DECLARE @F int
DECLARE @G int
DECLARE @H int
DECLARE @I int
DECLARE @K int
DECLARE @L int
DECLARE @M int
DECLARE @P int
DECLARE @EM int
DECLARE @ED int
SELECT
@A = @YearIn % 19,
@B = (@YearIn / 100), --INT(@YearIn / 100),
@C = @YearIn % 100
SELECT
@D = (@B / 4), --INT(@B / 4),
@E = @B % 4,
@F = ((@B + 8) / 25) --INT((@B + 8) / 25)
SELECT
@G = ((@B - @F + 1) / 3) --INT((@B - @F + 1) / 3)
SELECT
@H = (19 * @A + @B - @D - @G + 15) % 30,
@I = (@C / 4), -- INT(@C / 4),
@K = @C % 4
SELECT
@L = (32 + 2 * @E + 2 * @I - @H - @K) % 7
SELECT
@M = ((@A + 11 * @H + 22 * @L) / 451) --INT((@A + 11 * @H + 22 * @L) / 451)
SELECT
@EM = (( @H + @L - 7 * @M + 114) / 31), -- [3=March, 4=April] -- INT(( @H + l - 7 * @M + 114) / 31), -- [3=March, 4=April]
@P = (@H + @L - 7 * @M + 114) % 31
SELECT
@ED = @P + 1 -- (date in Easter Month)
SELECT
@ReturnDate = CAST(CAST(@YearIn AS CHAR(4)) + '-' + CAST(@EM AS CHAR(2)) + '-' + CAST(@ED AS CHAR(2)) AS datetime)
RETURN(@ReturnDate)
END
GO
The Need For Speed
The Holidays table and fn_FindDate function are great for calculating holidays and special dates, but their speed leaves something to be desired. So, I added a second table to store the calculated dates. This did need a few extra columns to help keep the calculated dates organized into the year they were created for. This was necessary as a special date can be created that would fall into a different year. For example, New Years, 2011 falls on a Saturday. My current job shifts holidays to the closest weekday. So, Jan 1st, 2011 would be celebrated on December 31, 2010 as the day the office is closed.
HolidayDates table:
| SysID | Unique Identifier used by the system. |
| HolidayID | Identifier for the Holidays table record used to create this date. |
| Year | Year used for creating the record (might not be the same as the year of the dates. |
| DayStart | Date the special day or holiday starts. |
| DayEnd | Date the special day or holiday ends (Will be the same or greater than DayStart). |
Code for creating the HolidayDates table:
IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[HolidayDates]') AND [type]='U'))
DROP TABLE [dbo].[HolidayDates]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[HolidayDates] (
[SysID] [int] IDENTITY(1, 1) NOT NULL,
[HolidayID] [int] NOT NULL,
[Year] [int] NOT NULL,
[DayStart] [datetime] NOT NULL,
[DayEnd] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [HolidayID_Year]
ON [dbo].[HolidayDates] ([HolidayID], [Year])
INCLUDE ([DayStart], [DayEnd])
ON [PRIMARY]
GO
Holidays and special dates don't change very fast so, once they are calculated, they can be stored in the table for faster retrieval with no need to recalculate every time. I use the following stored procedure, ds_Build_HolidayDates, to populate the table for any given year. The stored procedure is called as:
EXEC ds_Build_HolidayDates 2010
This will delete all date records in the HolidayDates table that have a creation year of 2010. Then it loops through the Holidays table and creates the actual dates in 2010 for each holiday and special date listed.
Code for ds_Build_HolidayDates:
IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[ds_Build_HolidayDates]') AND [type]='P'))
DROP PROCEDURE [dbo].[ds_Build_HolidayDates]
GO SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO CREATE PROCEDURE [dbo].[ds_Build_HolidayDates]
@inYear int AS
BEGIN
/*
Remove all created dates for the supplied year from HolidayDates table.
*/
DELETE FROM HolidayDates
WHERE
Year = @inYear
/*
Loop through all active (ReportFlag = 1) records in the Holidays table and find their active days for the year.
*/
INSERT INTO HolidayDates
SELECT
s1.HolidayID,
@inYear,
s1.DayStart,
DATEADD(dd, s1.DaysAfter, s1.DayStart) AS DayEnd
FROM
(
SELECT
HolidayID,
DaysAfter,
dbo.fn_FindDate
(
HolidayID,
Title,
ReportFlag,
DayOffFlag,
StartYear,
EndYear,
DaysPrior,
DaysAfter,
MonthOf,
DayOf,
NameDay,
WeekNumber,
LastDayFlag,
FullWeekFlag,
BaseHoliday,
DaysVariance,
SpecialCalc,
@inYear,
SlideDate
) AS DayStart
FROM Holidays
WHERE
StartYear <= @inYear
AND (EndYear = 0 OR EndYear >= @inYear)
AND (ReportFlag = 1)
) AS s1
END
GO
What's Happening Today
I do have 1 function that I use to tell me what holidays or special days are happening on a given day. This is used to load an active web calendar for employees to see what important dates are coming up. For each day of the month displayed I just run the following code for the activity:
SELECT
Title,
StartDate,
EndDate
FROM dbo.fn_ActivityForDay('2010-07-04') -- <current day from web page>
Code for fn_ActivityForDay:
IF (EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[fn_ActivityForDay]') AND ([type]='IF' OR [type]='FN' OR [type]='TF')))
DROP FUNCTION [dbo].[fn_ActivityForDay]
GO SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO CREATE FUNCTION [dbo].[fn_ActivityForDay]
(
@DateIn datetime
)
RETURNS @DaysActivities TABLE
(
Title varchar(100),
StartDate datetime,
EndDate datetime
)
AS
BEGIN
INSERT INTO @DaysActivities
SELECT
h.Title AS Title,
hd.DayStart AS StartDate,
hd.DayEnd AS EndDate
FROM HolidayDates AS hd
JOIN Holidays AS h ON
hd.HolidayID = h.HolidayID
WHERE
@DateIn BETWEEN hd.DayStart AND hd.DayEnd
RETURN
END
GO
Special Dates 4-4-5
I use the Holidays table to help solve my company's problem with keeping track of the billing cycle which I have seen termed as a 4-4-5 cycle. This was actually quite easy. All that is needed is to find the first billing cycle date and the rest fall right into place. I just created a date record for 4th Friday in January. This would have the following fields set.
| HolidayIDIn | 90 |
| TitleIn | 'Work Billing Cycle Close - January' |
| ReportFlagIn | 1 (True) |
| MonthOfIn | 1 (January) |
| NameDayIn | 6 (Friday) |
| WeekNumberIn | 4 (4th Friday in the month) |
These fields, with a given year, will give the first billing cycle end date. This also gives the base to be used to find the next billing cycle date. This billing cycle date would be defined as:
| HolidayIDIn | 91 |
| TitleIn | 'Work Billing Cycle Close - February' |
| ReportFlagIn | 1 (True) |
| BaseHolidayIn | 90 (January billing cycle) |
| DaysVarianceIn | 28 (4 weeks) |
To continue building on this, the next record would be:
| HolidayIDIn | 92 |
| TitleIn | 'Work Billing Cycle Close - March' |
| ReportFlagIn | 1 (True) |
| BaseHolidayIn | 91 (February billing cycle) |
| DaysVarianceIn | 35 (5 weeks) |
This pattern can be continued until all billing cycles are defined. Our company also has a week after the billing cycle close to process invoices with customers. These are usually the busier weeks as data gets collated, corrected, and billed to the customers. So, there is a special date defined for the full week after the billing cycle close.
| HolidayIDIn | 102 |
| TitleIn | 'Work Invoice Week - January' |
| ReportFlagIn | 1 (True) |
| DaysAfterIn | 4 (Monday - Friday) |
| BaseHolidayIn | 90 (January billing cycle) |
| DaysVarianceIn | 3 (All billing cycles close on Friday + 3 days = Monday) |
A record like this would be created for each billing cycle. The invoice week record would be linked to their respective billing cycle close record. This means that all the billing cycle and invoice week records are related and are based on the original January billing cycle.
In Closing
These tables and functions provide an easy way to record holidays and special dates in a way that they can be calculated for any given year. They also provide a way to create a series of inter-related special dates that can be accurately calculated year after year. I have found this method to be more stress free for me as once I have the special dates defined in the Holidays table, I don't have to worry about how to calculate the dates anymore. If the HolidayDates (calendar) table runs out a year early, all I need to do is run the ds_Build_HolidayDates for the next year and it's done. I actually did have a calendar table created by another person run out a year early. It was a lot easier for me to switch the reports to use the fn_FindDate function than it was to try to add several hundred date records to the old calendar table. I hope that you will be able to find this useful too. All code to recreate the tables and functions has been attached.
Have a good day,
Terry Steadman
