Handling Business-Related Date Tasks in T-SQL

Publication Date: December 31, 9999

Published in:

How many times have you found yourself in need of knowing what the next or previous business date is?

What about the dates when a particular quarter begins and ends? Have you had to handle the thorny issue of daylight savings time (DST); when does DST begin and end for this or another year in the US, UK or some other region? And finally, once you have determined you have a need to calculate these various date values, where do you host that logic? This article answers those questions.

Every business application in existence today has at least one data-centric requirement. For example, a typical report, form or data extract are often scoped by date. Sometimes you need a single effective date and at other times, you may need a date range. The twist in the business world is that most of the time, you are only concerned with business dates, which is a subset of all the calendar dates.

You won’t find a single definition of a business date. I like to think of a business date as a date on which business is transacted. Put another way, a business date is a date on which a company is open for business. Since the investment industry dominates the news so much today, I’ll use that industry as the vehicle to illustrate the business date concept.

Investment firms are normally open Monday through Friday. Accordingly, Saturday and Sunday are not business days. In addition, investment firms observe a number of holidays that have historically been referred to as bank holidays. That description, in reality, does not apply because today, most banks are open seven days a week. Nonetheless, “bank holiday” as a term of art still remains.

Bank holidays are days when the equity markets (NYSE, NASDAQ, etc.) are not open for trading. From this, you can deduce that a business day must occur on a weekday. However, not all weekdays are necessarily business days. Seems like a fairly trivial problem. As you will see, when met with the right solution, dealing with business dates can be a simple task. Business problems are never the problem. The problem usually rests with a proposed solution. For a long time I would deal with this issue on an ad-hoc, task-by-task basis. I found myself duplicating a lot of code, and in the process, violating the 10 commandments of sound programming.

Business problems are never THE problem. The problem usually rests with a proposed solution

OK then, let’s dive into the solution.

Geography: Where Should the Solution Reside?

The problem of how to calculate various business dates is inherently a data problem. With little effort, you could create a number of .NET classes to quickly and easily deal with the problem. In my opinion, .NET classes, while effective from an implementation standpoint, would be too limiting and thus would introduce new problems into the domain at hand.

I view SQL Server not so much as a database per se, but rather, the foundation of a robust solutions platform. It has a fairly rich programming environment which you can augment with .NET Assemblies. It also has a robust data engine. Finally, when you consider that the complete environment includes the entire Business Intelligence suite of tools (Reporting Services, Integration Services and Analysis Services), a good choice for the heart of the solution and the core logic is SQL Server. With this approach, you can implement a variety of interfaces to your middle-tier components and UIs.

Holidays Table

The only way SQL Server can know what the bank holidays are is if you provide SQL Server with the intelligence to make that determination. Business intelligence begins where data meets code. As a first step, you want to store the holiday data in a table. The following snippet shows a simple Holidays table structure:

CREATE TABLE Holidays(
   id int IDENTITY(1,1) NOT NULL,
   HolidayName [varchar](50) NOT NULL,
   HolidayDate [date] NOT NULL
)

Business intelligence begins where data meets code.

The US equity markets observe ten basic bank holidays. Figure 1 outlines the 2009 bank holidays stored in the Holidays table.

From a data design perspective, one could argue for a table to hold the bank holiday definitions and a child table to hold the date instances from year to year. I opted for a simpler single-table approach given the static nature of the data and I’ve based the code examples herein on that schema. Nonetheless, a parent-child approach is a sound approach as well.

With the data requirements squared away, the other foundational aspect of the solution rests with determining whether a given date is a business date.

The title of this magazine is CODE, so let’s write some.

Is the Passed Date a Business Date?

IsBusinessDate() is a scalar T-SQL function and is the programmatic foundation for the entire solution illustrated here. Listing 1 illustrates the IsBusinessDate() code. One important point to stress is that this code is predicated on SQL Server 2008, which introduced the long-awaited Date type. If you are using an earlier version of SQL Server, the convert function I used will result in a compile error because the Date type does not exist. For pre-SQL Server 2008 versions, you will need to manually strip out the time component of a DateTime value like this:

Set @CurrentDate =
convert(Varchar(10),getdate(),101)

Listing 1 shows a simple bit function that accepts a single date parameter. The default return value is set to 0 (false). Once a current date is ascertained, the first check is to see if the current date value falls on a weekday. If this is the case, the last check is to see if the date is found in the Holidays table. If the date is not found in the Holidays table, the return value is set to 1 (true), denoting the examined date as being a business date.

Figure 2 illustrates a call to the IsBusinessDate() function. As you can see, July 6, 2009 is evaluated as being a business date. July 3, 2009 is evaluated as not being a business day since that is the day Independence Day is observed in the USA.

Let’s extend things a bit to answer the more complex question of what the next or previous business date is based on a passed date.

Next and Previous Business Date Functions

At this point, you have a storage solution (data) for bank holidays and you have a mechanism to determine whether a given date is a business date (code). With a business intelligence foundation in place, you next want to determine the previous and next business dates based on a passed date. Listing 2 illustrates the code for the GetNextBusinessDate() and the GetPreviousBusinessDate().

As you can see, GetNextBusinessDate() and GetPreviousBusinessDate() consume IsBusinessDateFunction(). The only difference between the two functions is the second parameter in the DateAdd() call. Could I have collapsed these two functions into a single function that accepts an additional parameter to denote whether the previous or next business date is calculated? Yes, but I opted for a unitary design approach that has each function perform a single task. The same goes for the IsBusinessDate(). That function also performs a single task. Soundly written functions do one thing well. Functions written in a unitary fashion lend themselves to reusability.

Soundly written functions do one thing well. Functions written in a unitary fashion lend themselves to reusability.

Figure 3 illustrates how to use GetNextBusinessDate() and GetPreviousBusinessDate(). As you can see, for July 6, 2009, the previous business date is July 2nd because the Independence Day holiday was observed on July 3rd. And as you would expect, the next business day after July 6th is July 7th.

You definitely have something very useful here. With a little more work you can gain a lot more functionality. Now let me show you how to determine the current, previous and next month end dates.

Determining the Current, Previous and Next Month End Dates

Often you’ll get called upon to develop reports that look at data as of the last business day of the month. In addition, you often need to compare data from the previous two months. Listing 2 illustrates the three procedures used to determine the current, previous and next month end dates.

All of these functions consume GetPreviousBusinessDate(). For the current month date, the function uses the first calendar day of the next month as the date that is passed to GetPreviousBusinessDate(). For the previous month end date, the date passed to the GetPreviousBusinessDate() is simply the first calendar date of the current month. Finally, for the next month end date, the date used is the first calendar day of the current month + 2 (via DateAdd()).

Figure 4 illustrates the calls to the current, previous and next month end business date functions.

Calculate t-1 and t-2 Business Dates

Very quickly, you get a sense of how flexible this approach is. For example, what if you want to determine the previous two business dates (t-1 and t-2)? You already know how to get the previous business date based on a passed date. To the get the next previous date, you simply wrap one call in another as Figure 5 illustrates. If all you want to do is retrieve the previous two dates, this is not a problem. Trying to go further than that, you begin to see that this approach does not scale. For more complex cases, you should create procedures to encapsulate the logic necessary to retrieve more dates.

Determining Quarter Start and End Dates

In almost every business, the first day and last day of a quarter has some significance. The next set of functions makes use of the functions introduced in the last article. Listing 4 outlines the GetQuarterStartDate and GetQuarterEndDate functions.

The examples illustrated here are based on SQL Server 2008. If you are using a pre-SQL 2008 version, references to the Date Type will result in compiler errors.

Figure 6 illustrates a query and results that uses these functions to retrieve the quarter start and end dates for 2009.

As you can see, this example makes one rather large assumption. The assumption these functions make is that a calendar year, not a fiscal year, is involved. If your firm uses a fiscal year, these procedures can easily be adapted to that format.

Determining Daylight Savings Time Start and End Dates

Just as I found myself duplicating code to determine the appropriate business date, so too was I duplicating code to determine when daylight savings time (DST) would begin and end, and the impacts thereof. If your environment is solely in the USA, then knowing the precise time when DST begins and ends may not be a big deal. Nonetheless, if you have weekend jobs that could be affected, it may be nice to have a simple way to determine when DST begins and ends.

You might think you have to have a reference table store the dates for you. That is certainly a valid approach. However, it does require that somebody actually maintain the data. Recently, I was working with a global trading system that had such a facility. When I opted to refer to the data to determine whether the current time period was during DST, I quickly found out that no date evaluated to being under Daylight Savings Time for a very good reason-the reference data was not updated! I am of the opinion that under some circumstances, data point-driven rules can be , and often should be, expressed in code. To some database folks, this smacks of heresy. If the rules are static, not subject to change and if the code is simple, then embedding the data with the code is not a bad idea. Seriously, how often do the rules for DST change? If you have a reference table, you need to maintain that on an annual basis, right? Whether you are maintaining code or data, it really should not matter-as long as your code adheres to unitary principles (do one thing and do that one thing well.) In the case of determining DST for the USA, the rule is very simple. DST begins the second Sunday in March at 2:00 AM and ends the first Sunday in November at 2:00 AM.

Under some circumstances, data driven rules can be and often should be expressed in code

Listing 5 illustrates the functions used to determine when DST begins and ends.

As with the previous examples, the code to determine when DST begins and ends in the USA is not that complicated. You simply begin with a starting point and move through the dates based on the rule.

What if you are in a situation where you have to deal with multiple DST rules? If part of your operation is in London, England that is precisely the kind of situation you have to deal with.

Dealing with DST in an International Context

Consider this hypothetical-your location is in the USA but your data is hosted in London, England. This means the timestamps associated with the data are based on London time. For example, if you entered data at 3:00 PM (Eastern Time), the data is recorded as 8:00 PM London Time. Is that correct? It may be correct. Then again, it may not be correct. Let me put it this way, 48 weeks out of the year, the aforementioned assumption will be true. Three weeks out of the year, that same assumption will be incorrect. For those three weeks, the offset between Eastern Time and London Time will be four, not five hours. Why? The reason is Daylight Savings Time. And, more specifically, it is because the DST rules for the USA and Europe are not the same.

In Europe, DST begins the last Sunday in March at 1:00 AM and ends the last Sunday in October at 1:00 AM. Listing 6 illustrates the functions that determine the European DST start and end dates.

…the DST rules for the USA and Europe are not the same

Figure 7 illustrates the basic issue. Using 2009 as an example, you can see that in the USA, DST begins on 3/8 @ 2:00 AM and ends on 11/1 @ 2:00 AM. In Europe, DST begins on 3/29 @ 1:00 AM and ends on 10/25 @ 1:00 AM. From this, you know that for four weeks, the USA is on DST when Europe is not. Three of these weeks are in March and the other is the last week in October. This means that for four weeks of the year, the time difference between the Eastern USA and London is four, not five hours. If you have processes that assume the five-hour difference is a constant, you may run into problems. Often companies just suck it up and manually tweak their code to account for the difference and reset the code once both regions are under DST. This brute force approach will “work” but it is a solution that is far from optimal.

I’ll show you a business intelligence solution to automate a solution. Before I get to that solution, first take a look at an illustration of the DST permutations that exist between the USA and Europe. Figure 8 illustrates the three permutations:

Both USA and Europe on DST
Only USA on DST
Neither USA or Europe on DST

Listing 7 illustrates a handy function to determine the correct offset to use based on a date parameter.

As you can see, the code is very simple and straightforward. By default, the offset is set to 5 hours. When the USA is on DST and Europe is not, the time offset is set to 4. Figure 9 illustrates how you can use this function.

Before moving on to ways you can apply some of these date functions, you have probably already thought of ways to apply and modify these functions in your context. In the course of my work, these date functions have saved me a lot of time, both in terms of maintenance and development. I hope you find them useful.

Applying the Date Functions

I’ll use a stubbed orders table as the basis for the examples.

CREATE TABLE [dbo].[Orders](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderDescription] [varchar](50) NOT NULL,
[OrderDate] [date] NOT NULL)

In actual practice, an orders table would be much more complex. But for our purposes here, this simple structure will suffice.

The hypothetical requirement to illustrate these date concepts is simple: extract orders for the previous business day or for any specific date that is requested. This process could be triggered, manually, via a SQL Agent Job or via a report. For this article, I’ll concentrate on two elements:

A SQL Server stored procedure
A SQL Server Reporting Services report

Creating the Stored Procedure

Listing 8 illustrates the procedure to retrieve the orders. If the calling program doesn’t pass a specific date, the procedure will use the previous business date as the default. Figure 10 illustrates a call that invokes the default and a call that uses a specific date.

Creating the Report

Figure 11 illustrates a simple orders report. I am not going to delve into the specifics of how to create/deploy reports in SQL Server Reporting Services (SSRS). You can find plenty of material in the CODE Magazine archives (code-magazine.com) and MSDN (msdn.com) to get you started. Instead, I will highlight how to take advantage of the server-based date logic within an SSRS solution.

Figures 12 and 13 illustrate a way to consume our previous business date logic hosted in SQL Server. The first step is to create a dataset that will hold the date value. Once the date value specification is available, that value can be used as a default value for the report parameter. With the default in place, as soon as the report is loaded, the default value is used to retrieve the order data. From that point on, if you wish to use another date as the date parameter, you are free to do so.

Conclusion

Having reusable code to calculate various business dates will prove to be a big time saver for you. From the basic functions illustrated here you can easily extend their functionality to determine quarter start/begin dates. It is also relatively simple to determine when daylight savings time begins and ends in an international context. It is also very simple to implement these functions with business intelligence tools like SQL Server Reporting Services.

No longer will your client have to have the embedded logic to determine the next or previous business date. Because SQL Server hosts the logic, your SQL stored procedures can natively take advantage of the techniques herein.

Listing 1: IsBusinessDate() function

CREATE FUNCTION [dbo].[IsBusinessDate](@CurrentDate Date = Null)
RETURNS Bit
AS
BEGIN
   Declare @RetVal Bit
   Set @RetVal = 0
   
   If @CurrentDate is Null
      Begin
          Set @CurrentDate = convert(date,getdate(),101)
      End
   
   If datepart(dw,@CurrentDate) >= 2 And
datepart(dw,@CurrentDate) <= 6
       -- Passed date is not a Saturday or Sunday.
      Begin
         If Not Exists (Select HolidayDate From Holidays where
HolidayDate = @CurrentDate)
            -- Passed date is not found in Holidays table.
            Begin
                -- The passed date is a business date.
                Set @RetVal = 1
            End
      End
          
   Return @RetVal
END

Listing 2: GetNextBusinessDate and GetPreviousBusinessDate functions

Create FUNCTION [dbo].[GetNextBusinessDate](@CurrentDate Date =
Null)
RETURNS Date
AS
BEGIN
      
   Declare @RetVal Date
   Declare @IsBusinessDate Bit
   
   Set @IsbusinessDate = 0
   
   If @CurrentDate is Null
      Begin
          Set @CurrentDate = convert(date,getdate(),101)
      End
      
      Set @RetVal = @CurrentDate
      
   While @IsBusinessDate = 0
       -- Continue searching for the next business date.
       -- A date that is not a Saturday, Sunday or found in
       -- the Holidays table.
      Begin
         Set @RetVal = DateAdd(dd,+1,@RetVal)
         Set @IsBusinessDate = dbo.IsBusinessDate(@RetVal)
      End
      
   Return @RetVal
END
      
CREATE FUNCTION [dbo].[GetPreviousBusinessDate](@CurrentDate
   Date = Null)
RETURNS Date
AS
BEGIN
      
   Declare @RetVal Date
   Declare @IsBusinessDate Bit
   
   Set @IsbusinessDate = 0
   
   If @CurrentDate is Null
      Begin
          Set @CurrentDate = convert(date,getdate(),101)
      End
      
      Set @RetVal = @CurrentDate
      
      
   While @IsBusinessDate = 0
       -- Continue searching for previous business date.
       -- A date that is not a Saturday, Sunday or found in the
       -- Holidays table.
      Begin
         Set @RetVal = DateAdd(dd,-1,@RetVal)
         Set @IsBusinessDate = dbo.IsBusinessDate(@RetVal)
      End
      
   Return @RetVal
END

Listing 3: GetCurrentMonthEndDate, GetPreviousMonthEndDate and next GetNextMonthEndDate functions

CREATE FUNCTION [dbo].[GetCurrentMonthEndDate]
(
   @CurrentDate Date = Null
)
RETURNS Date
AS
BEGIN
      
If @CurrentDate is Null
   Begin
      Set @CurrentDate = convert(date,getdate(),101)
    End
      
    Return
 dbo.GetPreviousBusinessDate(Convert(varchar(2),Datepart(mm,DATEADD
(mm,+1,@currentdate))) + '/01/' +
Convert(Varchar(4),Datepart(yyyy,DATEADD(mm,+1,@currentdate))))
END
      
CREATE FUNCTION [dbo].[GetPreviousMonthEndDate]
(
   @CurrentDate Date = Null
)
RETURNS Date
AS
BEGIN
      
If @CurrentDate is Null
   Begin
      Set @CurrentDate = convert(date,getdate(),101)
    End
      
    Return
      
dbo.GetPreviousBusinessDate(Convert(varchar(2),Datepart(mm,@current
date)) + '/01/' +
           Convert(Varchar(4),Datepart(yyyy,@currentdate)))
END
      
Create FUNCTION [dbo].[GetNextMonthEndDate]
(
   @CurrentDate Date = Null
)
RETURNS Date
AS
BEGIN
      
If @CurrentDate is Null
   Begin
      Set @CurrentDate = convert(date,getdate(),101)
    End
      
    Return
dbo.GetPreviousBusinessDate(Convert(varchar(2),Datepart(mm,DATEADD(
mm,+2,@currentdate))) + '/01/' +
   Convert(Varchar(4),Datepart(yyyy,DATEADD(mm,+2,@currentdate))))
END

Listing 4: Quarter start and end date functions

CREATE FUNCTION [dbo].[GetQuarterStartDate]
(
   @Quarter int,@year int
)
RETURNS Date
AS
BEGIN
      
    If @Quarter = 1
       Begin
          Return dbo.GetNextBusinessDate(dbo.getPreviousMonthEnddate(
Convert(date,'01/01/'+convert(varchar(4),@year),101)))
       End
       
    If @Quarter = 2
       Begin
          Return dbo.GetNextBusinessDate(dbo.getPreviousMonthenddate(
Convert(date,'04/01/'+convert(varchar(4),@year),101)))
       End
       
    If @Quarter = 3
       Begin
          Return dbo.GetNextBusinessDate(dbo.getPreviousMonthEndDate(
Convert(date,'7/01/'+convert(varchar(4),@year),101)))
       End
      
    If @Quarter = 4
       Begin
          Return dbo.GetNextBusinessDate(dbo.getPreviousMonthEndDate(
Convert(date,'10/01/'+convert(varchar(4),@year),101)))
       End
      
Return Null
      
END
      
Create FUNCTION [dbo].[GetQuarterEndDate]
(
   @Quarter int,@year int
)
RETURNS Date
AS
BEGIN
   Declare @RetVal Date
   Set @RetVal = Null
      
    If @Quarter = 1
       Begin
          Return dbo.GetPreviousBusinessdate(
Convert(date,'04/01/'+convert(varchar(4),@year),101))
       End
       
    If @Quarter = 2
       Begin
          Return dbo.GetPreviousBusinessdate(
Convert(date,'07/01/'+convert(varchar(4),@year),101))
       End
       
    If @Quarter = 3
       Begin
          Return dbo.GetPreviousBusinessdate(
Convert(date,'10/01/'+convert(varchar(4),@year),101))
       End
      
    If @Quarter = 4
       Begin
          Return dbo.GetPreviousBusinessdate(
Convert(date,'01/01/'+convert(varchar(4),@year+1),101))
       End
      
   Return Null
      
      
END

Listing 5: Daylight Savings Time Begin/End functions-USA

CREATE FUNCTION [dbo].[GetDSTEndDateUSA]
(
   @year int = Null
)
RETURNS DateTime
AS
BEGIN
      
Declare @RetVal Datetime
Declare @SundayCount int
      
If @year is null
   Begin
      Set @year = DatePart(yyyy,getdate())
   End
      
Set @SundayCount = 0
--first sunday in November 2:00 AM - End
      
Set @RetVal = '11/01/' + Convert(varchar(4),@year)
      
While @SundayCount <> 1
   Begin
      If DATEPART(dw,@RetVal) = 1
         Begin
            Set @SundayCount = @SundayCount + 1
            If @SundayCount = 1
               Begin
                  Break
               End
         End
         
         Set @RetVal = DATEADD(dd,1,@RetVal)
   End
      
Return convert(datetime,@RetVal + '02:00:00 AM',101)
      
END
      
CREATE FUNCTION [dbo].[GetDSTStartDateUSA]
(
   @year int = Null
)
RETURNS DateTime
AS
BEGIN
      
Declare @RetVal Datetime
Declare @SundayCount int
      
If @year is null
   Begin
      Set @year = DatePart(yyyy,getdate())
   End
      
Set @SundayCount = 0
--second sunday in March 2:00 AM - Start
      
Set @RetVal = '03/01/' + Convert(varchar(4),@year)
      
While @SundayCount <> 2
   Begin
      If DATEPART(dw,@RetVal) = 1
         Begin
            Set @SundayCount = @SundayCount + 1
            If @SundayCount = 2
               Begin
                  Break
               End
         End
         
         Set @RetVal = DATEADD(dd,1,@RetVal)
   End
      
Return convert(datetime,@RetVal + '02:00:00 AM',101)
      
END

Listing 6: Daylight Savings Time Begin/End functions-Europe

CREATE FUNCTION [dbo].[GetDSTStartDateEUROPE]
(
   @year int = Null
)
RETURNS Datetime
AS
BEGIN
      
Declare @RetVal Datetime
      
If @year is null
   Begin
      Set @year = DatePart(yyyy,getdate())
   End
      
--Last sunday in March 1:00 AM
      
Set @RetVal = '03/31/' + Convert(varchar(4),@year)
      
While datepart(dw,@Retval) <> 1
   Begin
      Set @Retval = DATEADD(dd,-1,@Retval)
   End
      
Return Convert(datetime,Convert(varchar(10),@retval,101) + '
01:00:00 AM',101)
      
END
      
CREATE FUNCTION [dbo].[GetDSTEndDateEUROPE]
(
   @year int = Null
)
RETURNS Datetime
AS
BEGIN
      
Declare @RetVal Datetime
      
If @year is null
   Begin
      Set @year = DatePart(yyyy,getdate())
   End
      
--Last sunday in October 1:00 AM
      
Set @RetVal = '10/31/' + Convert(varchar(4),@year)
      
While datepart(dw,@Retval) <> 1
   Begin
      Set @Retval = DATEADD(dd,-1,@Retval)
   End
      
Return Convert(datetime,Convert(varchar(10),@retval,101) + '
01:00:00 AM',101)
      
END

Listing 7: Determine Daylight Savings Time offset

CREATE FUNCTION [dbo].[GetDSTOffSetUSA_Europe]
(
   @currentdate datetime = Null
)
RETURNS TinyInt
AS
BEGIN
   
   Declare @DSTUSA bit
   Declare @DSTEurope bit
   Declare @retval TinyInt
   
   Set @retval = 5
   
   If @CurrentDate is Null
   Begin
      Set @CurrentDate = getdate()
   End
  
   Set @DSTUSA = dbo.IsDSTUSA(@CurrentDate)
   Set @DSTEurope = dbo.IsDSTEurope(@CurrentDate)
   
   IF @DSTUSA = 1 And
      @DSTEurope = 0
         Begin
            Set @RetVal = 4
         End
   
   Return @retval
END

Listing 8: Stored procedure to retrieve orders for a specific date

CREATE PROCEDURE GetOrders
(@OrderDate Date = Null)
AS
BEGIN
      
IF @OrderDate is null
   Begin
      Set @OrderDate = Utility.dbo.GetPreviousBusinessDate(Null)
   End
      
select *
  from Orders
  Where OrderDate = @OrderDate
END