T-SQL Function Finds Almost Any Kind of Date

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 [email protected] 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