Generating a List of Standard Business Holidays

EDITOR'S NOTE: Share your T-SQL tips and tricks with other T-SQL Solutions readers. Email your ideas (400 words or fewer) to [email protected]. If we print your submission, you'll get $50.

I recently had to write interface code in which I recreated employee timesheets for a project-accounting application from timesheets in a customer relationship management (CRM) application. I had to convert costs to account for increased pay for hourly employees when they worked on a holiday, but neither the CRM application nor the project-accounting application had a good way to identify standard business holidays. To remedy this problem, I wrote a piece of T-SQL code that generates standard business holidays—New Year's Day, Memorial Day, 4th of July, Labor Day, Thanksgiving, and Christmas—for a range of years.

Listing 1 shows the script I wrote. First, I had to address two related localization problems. Because I used the DATEPART(dw, ) function to get an integer representing the day of the week, I needed to make sure that I explicitly set the first day of the week. In the United States, the default first day of the week is Sunday, which has an @@DATEFIRST value of 7. I didn't want to take my DATEFIRST setting for granted, though, so I used SET DATEFIRST 7 to explicitly set the first day of the week to Sunday. (For information about how DATEFIRST and DATEPART work together, see the sidebar "The DATEFIRST and DATEPART Relationship.")

The second localization problem was the date format for the database management system (DBMS) language. In the code, I'm converting dates from strings to an explicit datetime data type. The string dates are in the US English format mm/dd/yyyy. In Europe, most languages would treat the same string as dd/mm/yyyy. To preclude ambiguity and out-of-range conversion errors, the code in Listing 1 uses SET LANGUAGE N'us_english' to ensure that when I use the mm/dd/yyyy format, the code returns the dates I expect. For your reference, you can find settings for the DATEFIRST value and date formats for different languages in the master database's syslanguages table. Incidentally, when you set the language for the script, if you don't explicitly set DATEFIRST to 7, SQL Server will set the DATEFIRST value to the value for the language you specified. When you explicitly set DATEFIRST, it overrides the syslanguages value, even when you use SET LANGUAGE after SET DATEFIRST.

Next, the code sets up two table variables: @year to hold the range of years and @day to hold the range of days in a month. When I originally wrote this script in SQL Server 7.0, @year was a scalar variable and @day was a temporary table called #DAY. When I used the SQL Server 7.0 @year scalar variable, the set of holidays that the code returned included dates in only one year. This setup didn't work well when the period I converted overlapped the New Year and included both Christmas and New Year's Day. Using the SQL Server 2000 @year table variable elegantly simplified the problem. Instead of @year having a single scalar value (e.g., 2002), I was able to enter a range of values in the @year table variable (e.g., 2002, 2003, 2004).

After setting up the table variables, I started working through the holidays to return a result set. Generally, businesses have rules for two types of holidays. One type (e.g., Christmas, New Year's Day, 4th of July) fall on fixed dates but move to Friday or Monday when they fall on Saturday or Sunday, respectively. The other type (e.g., Memorial Day, Labor Day, Thanksgiving) have variable dates, such as the first Monday or the fourth Thursday of the month.

To return fixed-date holidays, I used a CASE statement. When New Year's Day, for example, falls on the seventh day of the week, Saturday—DATEPART(dw,) = 7—I use DATEADD() to subtract a day. Using this function is easier than entering 12/31 and subtracting a year from the YEAR_STR char value in the @year table. When DATEPART() returns 1—or Sunday—I add a day. If the day of the holiday is a weekday, I make no changes.

To return variable-date holidays, I use a MAX() or MIN() function. Memorial Day, the last Monday in May, uses a MAX() function with DATEPART(dw,) = 2. Labor Day, the first Monday in September, uses the MIN() function to return the first Monday. Thanksgiving is interesting because it's the fourth Thursday (not necessarily the last Thursday). By limiting the DayOfMonth value in the @DAY table to less than 29 and using the MAX() function to generate a DATEPART(dw,) = 5 (Thursday) value, I ensure that I get the fourth Thursday. For September's Labor Day, I limit the DayOfMonth value to less than 30 so that I don't get a date-out-of-range conversion error for the nonexistent date 9/31. I could have used BETWEEN clauses for the DayOfMonth instead of the MAX() or MIN() functions, but I chose the latter because the functions provide slightly more consistency when I copy and paste to get different days. I included the third Monday in January, Lee-Jackson-King Day (a state holiday here in Virginia), by limiting the DayOfMonth to less than 22. I could have said BETWEEN 15 AND 21—and avoided using the MAX() function—but the method I chose was conceptually easier to code.

Two other syntactical details are worth noting. First, if you're including this code snippet as a subquery by designating the column names Holiday, Year, and Date in the first union for New Year's Day, you can refer to these column names from the main query. Second, the ORDER BY 3 clause after the Christmas SELECT statement is an example of a simple way to order the whole structure by referring to a column number rather than a column name or calculation. This method is particularly effective for UNION statements because the column names across multiple tables in the union could be different.

I'm not sure T-SQL will account for every holiday. I'm having a hard time with Easter—the first Sunday after the first full moon after the spring equinox (in the Christian West). Fortunately, in my case, Easter isn't a standard business holiday. Maybe you can figure that one out. And don't get me started comparing Gregorian Christmas (which most civil governments and businesses call Christmas) with the old Julian Christmas (currently 14 days after the Gregorian Christmas). I hope the tips you learn here and throughout T-SQL Solutions let you spend your next holiday relaxing on the beach.

—Chuck Hawkins
[email protected]