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,
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,
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,
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.
