Executive Summary:
Do you work with Microsoft SQL Server 2005 or Microsoft SQL Server 2000 and often need to find certain dates, such as the first Tuesday of next month or the next holiday? Do you often have to determine whether the date a query returns is a working day, weekday, or company holiday? If so, Michael Berry has a solution for you. His solution uses a T-SQL function named Dates and a table named Calendar. |
While working on projects, we all create cool code
that we think would be useful to share with others.
The best code in the world is borrowed code because it’s
free and typically battle-hardened. Last year, I found some
code that creates a Calendar table. As its name suggests,
this table includes calendar data, which you can customize.
I created the Dates function to find almost any kind of
date in the Calendar table. For example, you can use the
function to do the following:
- Find the first Tuesday of the next month.
- Determine whether the date your query just returned is
a working day, weekday, or company holiday.
- Create recurring-date functionality like that in Microsoft
Outlook.
The Dates function is flexible and easy to maintain
because it uses a matrix and simple math to locate the
requested target date. Table 1 shows the matrix. To use
the Dates function, you select one item from each column
in the matrix and use
each item’s bold letter
to form an acronym
representing your target
date. For example,
if you want the Dates function to return the next holiday
next month, you would use the acronym NHNM when you
execute the function with the statement
SELECT f_dates ‘NHNM’
Using the NHNM example, let’s look at how to use the
Dates function in more detail and how it works. But before
we do, there are two options—“Last” and “Previous”—in
the “This” column I need to explain. You use the “Last”
option to indicate that you want the last occurrence of
something, such as the last holiday. You use the “Previous”
option to indicate that you want the occurrence
before the last occurrence. For example, if today’s date is
December 31, the “Last” holiday would be Christmas and
the “Previous” holiday would be Thanksgiving.
To take advantage of the Dates function, you must
first create and customize the Calendar table. On SQL
Server Magazine’s Web site, you’ll find CodeToCreate-
CalendarTable.sql, which you can run to create this table.
(Go to www.sqlmag.com, enter 97590 in the InstantDoc
ID text box, then click the 97590.zip hotlink.) The script
automatically populates the table with standard holidays,
so you only need to add a column for company-specific
holidays and manually add those dates.
On SQL Server Magazine’s Web site, you’ll find also find
DatesFunction.sql, which contains the code for the Dates
function. This function works on SQL Server 2005 and
SQL Server 2000. The Dates function has two mandatory
parameters: the @eDate parameter, which specifies the
current date, and the @Parm parameter, which is the
acronym specifying the target date (in this case, NHNM).
The function also has two optional parameters—@Day-
Name and @xth—that you don’t need
to set for the NHNM example, so I
won’t cover them in detail here. The @
DayName parameter is used to find a
day of week. For example, you can use
it to find the next Monday this month.
When you select the “Xth” option in
the “Which” column, you use the @xth
parameter. Xth is just like Nth. With the
“Xth” option, you can find, for example,
the third Wednesday next month.
Playing with all the function’s options
and parameters is the best way to learn
how to use the function and to see how flexible it is.
The Dates function divides the acronym in the @Parm
parameter into separate parts based on each letter’s position
by using the code
SELECT @A = SUBSTRING(@Parm,1,1)
SELECT @B = SUBSTRING(@Parm,2,1)
SELECT @C = SUBSTRING(@Parm,3,1)
SELECT @D = SUBSTRING(@Parm,4,1)
So, in our NHNM example, @A = N, @B = H, @C =
N, and @D = M.
To calculate the target date, the Dates function
first uses the @C and @D variables to determine the
number of days in the specified period (e.g., this week,
next week, last month, next month) and assigns this
number to @Part2 variable. For example, Listing 1 shows
code that counts the number of days when the specified
period is next month (i.e., @C = N and @D = M).
This code is part of a large CASE
function in DatesFunction.sql. The
CASE function goes through each of
the possible 16 @C and @D combinations,
such as:
- @C = T and @D = W
- @C = L and @D = W
- @C = N and @D = W
- @C = P and @D = W
- @C = T and @D = M
- @C = L and @D = M
Next, the Dates function
determines the base date. The
best way to explain the base
date is through some examples.
To determine the base date, the
function uses the last two characters
in @Parm and the date
in @eDate. For example, if the
specified period is last week (@
C = L and @D = W) and the
current date is 12/31/2007, the
base date would be 7 days ago,
or 12/24/2007. If the specified
period is next week (@C = N
and @D = W) and the current
date is 12/31/2007, the base date
would be 7 days in the future, or
01/07/2007. As Listing 2 shows,
the function adds the number
of days in the specified period
(@Part 2) to the current date
(@eDate) to get the base date.
The function then retrieves all
the fields from the Calendar
table for that date and assigns
the data to a table variable
named @hold. Although all
the fields aren’t necessary for the
purposes here, I added them in
case I want to add more functionality
in the future.
Because @hold contains all the fields, the Dates
function uses a SELECT statement to retrieve just the
date (i.e., the value in the dte field), which it assigns to
the @HoldDate variable. The function uses @HoldDate
to calculate the beginning date and ending date for the
requested specified period and sets those dates to the @
BaseStartDate and @BaseEndDate variables, respectively.
Listing 3 shows how the function calculates
the beginning date and Listing 4 shows how
the function calculates the ending date for
the NHNM example. The code excerpts in
both listings are part of large CASE functions
in DatesFunction.sql.
With the beginning and ending dates in
hand, the Dates function creates a working
table. The Dates function then uses the
working table along with the values in the
@A and @B variables to obtain the target
date. Remember that, in this example, the
target date is the next holiday next month, so
@A is N and @B is H. Thus, the function uses
the code in Listing 5 to find the target date.
You can create a Visual Basic (VB) application
that provides an interface for the Dates
function so that other people can easily use it. For example,
Figure 1 shows
the interface for
a VB 6.0 application
I created for
developers at my
company who
want to use the
Dates function in
their programs.
After the developers
make their
selections in the
interface, the application displays the appropriate SQL
SELECT statement in the SQL Syntax textbox so that
they can copy and use it. The application also returns the
value for that date so that they can make sure they made
the correct selections and that the results are as expected.
In Figure 1, notice that there are a few more options
compared with the options in the matrix in Table 1. The
Dates function in DatesFunction.sql is more generic than
the Dates function used at my company so, for example,
the trade day options you see in Figure 1 aren’t in Dates-
Function.sql.
For me, the Dates function has proved its usefulness
countless times because it’s so versatile. You can use it
for just about any type of date that’s important in your
environment (e.g., financial closing dates, processing
dates) by adding those dates to the Calendar table and, if
needed, updating the matrix and DatesFunction.sql. The
possibilities are endless, and I’d love to see any updates
you make to the Dates function. You can contact me at
michaelberry67@yahoo.com if you have questions about
the function or you want to share your version of it.
— Michael Berry, Senior DBA, Ohio Public Employees Retirement System
End of Article
