Finding Out if a Date Falls on a Business Day
By Vakhtang Pavliashvili

In many business applications it is a common requirement to determine whether a particular date falls on a business day. The business rules utilizing this fact could vary: Some employees get paid overtime for working on non-business days; certain organizations only allow transactions on business days, and so forth. Figuring out whether a date is a business day can be fairly straightforward in Transact SQL code (which is the SQL Server flavor of the Structured Query Language). However, you need to be aware of how regional settings work in Microsoft SQL Server 2000. This article teaches you some of the built-in date related functions and settings available in SQL Server. I will demonstrate how to build a user-defined function, which lets us determine whether a given date falls on a business day.

Before delving into the details of coding, some introductions are in order. There are several useful date related built-in functions in SQL Server 2000. For example, you can use the DATEPART function to retrieve any part of the date variable provided. This function takes in two parameters: the part of the date you wish to retrieve and the date itself. The DATEPART function returns an integer representing any of the following parts of the supplied date: year, quarter, month, day of the year, day, week number, weekday number, hour, minute, second or millisecond.

For example, suppose I wish to retrieve the week number of September 24^th of 2000. I can use the following code:

SELECT DATEPART(WEEK, 'Sep 24 00')
Result:
-----------
40

Similarly, if I wish to know which weekday a particular date falls on, I can use the WEEKDAY keyword with the DATEPART function:

SELECT DATEPART(WEEKDAY, 'Sep 24 00 11:05:00'  )
Result:
-----------
2

As you can guess from the examples above, the DATEPART function can be very useful, but what if you need to know the name of the weekday? The DATENAME function does exactly that. Just as the DATEPART function, the DATENAME function also accepts two parameters – portion of the date, the name of which you wish to know and the date. The DATENAME function can be used to retrieve any of the following: the name of the year, quarter, month, day of the year, day, week, weekday, hour, minute, second or a millisecond of the specified date. For instance, I can determine the weekday name as well as month name of a given date as follows:

SELECT DATENAME(WEEKDAY, '9/25/00 11:05:00PM' ), DATENAME (MONTH, '9/25/00 11:05:00PM' )

Result:

--------------------------- ------------------------------
Monday                      September

With prior releases of SQL Server you could not create user-defined functions, so if you needed a function that was not supported by the software your options were somewhat limited. You could create a stored procedure that acted like a function (by returning a single value) or you had to use coding languages other than Transact-SQL to implement the desired functionality. With SQL Server 2000 you can create your own user-defined functions to supplement the needs of your applications. Since SQL Server does not provide a built-in function for determining whether a date falls on the business day, we can code it ourselves.

Now back to the task at hand: most countries consider Monday through Friday as workdays and Saturday and Sunday as weekends. Therefore, in order to determine whether a date is a business day all we have to do is use the DATENAME function with the WEEKDAY keyword. We could examine the output of the DATENAME function and see if it returns ‘Saturday’ or ‘Sunday’ right? If so, we could easily code the solution as follows:

CREATE FUNCTION fn_isbusinessday (@date DATETIME)
RETURNS BIT -- 1 for 'yes', 0 for 'no'
AS
BEGIN
DECLARE @return BIT

IF DATENAME(WEEKDAY, @DATE) IN ('saturday', 'sunday')
                BEGIN
                                SELECT @return = 0
                END
ELSE
                BEGIN
                                SELECT @return = 1
                END
RETURN @return
END

Then we could call the function we just created with the following code:

SELECT dbo.fn_isbusinessday('Oct 5 01')

Result:
----
1

This solution would work fine if you’re using English as your language, but it would fail in any other regional setting. Check out what happens if I change my language setting to German:

SET LANGUAGE 'GERMAN'
GO
SELECT @@LANGUAGE
SELECT DATENAME(WEEKDAY, 'Sep 30 01')

Result:
Changed language setting to Deutsch.
------------------------
Deutsch
------------------------------
Sonntag

Executing the fn_isbusinessday function would return the wrong results, because the output of the DATENAME function is not ‘Saturday’ or ‘Sunday’:

SELECT dbo.fn_isbusinessday ('Sep 30 01')

Result:
----
1

Let’s see if we can modify fn_isbusinessday function so that it fits any regional setting.

Since we can’t use DATENAME in our user-defined function, we will have to resort to the DATEPART function, which returns an integer. This is where things can get a little confusing.

The first day of the week can vary from one country to the next. In the United States the week begins on Sunday. Some of the European countries, on the other hand, consider Monday as the first day of their week. With SQL Server the first day of the week is determined by the value of the global variable @@DATEFIRST. To change the value of this variable you have to use the SET DATEFIRST statement, as follows:

SET DATEFIRST 7

The catch is that @@DATEFIRST considers Monday to be the day number 1, Tuesday to be the day number 2, and so forth. Therefore, if your @@DATEFIRST setting equals to 7, that means the first day of the week is Sunday. On the other hand, when using the DATEPART function for retrieving weekday numbers, the day number 7 is Saturday (assuming that @@DATEFIRST is set to 7), Sunday being the day number 1. For instance, the following query returns 1, because September 30^th, 2001 happens to fall on Sunday:

SELECT DATEPART(WEEKDAY, '9/30/01')

Result:
-----------
1

The following table summarizes the differences between the values returned by @@DATEFIRST and DATEPART function:

In order for the program to be useful in all regional settings we have to check for @@DATEFIRST setting. Keep in mind though, that as we change the @@DATEFIRST value, weekday numbers change as well.

For instance if we decide to start the week on Saturday instead of Sunday, we’ll get a different result with the same query as above:

SET DATEFIRST  6
SELECT DATEPART(WEEKDAY, '9/30/01')

Result:
-----------
2

Therefore, to determine whether a particular date happens to be a business day, we need to account for each possible value of @@DATEFIRST and respective values for each weekday returned by the DATEPART function. The global variable @@DATEFIRST can take on any value between 1 and 7. We can investigate how the DATEPART function values change as we modify the value of @@DATEFIRST by executing the following code:

SET NOCOUNT ON          -- turn off counting of rows

-- create a table variable to store weekday names and numbers:
DECLARE @temp_table TABLE (
weekday_name VARCHAR(20),
weekday_number TINYINT,
DATEFIRST_value TINYINT)

-- declare and populate variables:
DECLARE @date SMALLDATETIME, @n TINYINT, @m TINYINT
SELECT @date = '9/30/01', @n = 1, @m = 1

/* run through all possible values of @@DATEFIRST and find
** appropriate weekday numbers:
*/

WHILE @n < 8
BEGIN
SET DATEFIRST @n
                WHILE @m < 8
                BEGIN
                INSERT @temp_table
                SELECT DATENAME(WEEKDAY, DATEADD(dd, @m, @date)),
                DATEPART(WEEKDAY, DATEADD(dd, @m, @date)),
                @@DATEFIRST
                SELECT @m = @m + 1
                END
SELECT @n = @n + 1, @m = 1
END

-- return the results:
SELECT * FROM @temp_table

Results:

From these results we can determine the weekday numbers for business days for each possible @@DATEFIRST value. The following table summarizes these facts:

Now that we have done all the preliminary work, it’s time to modify our function fn_isbusinessday so that it’ll work regardless of the regional settings:

ALTER FUNCTION fn_isbusinessday (@date DATETIME)
RETURNS BIT -- 1 for 'yes', 0 for 'no'
AS
BEGIN
DECLARE @return BIT

IF @@DATEFIRST = 7 -- Sunday
                BEGIN
                                 IF DATEPART(WEEKDAY, @date)  BETWEEN 2 AND 6
                                                BEGIN
                                                GOTO is_business
                                                END
                                 ELSE
                                                BEGIN
                                                GOTO is_not_business
                                                END
                END

IF @@DATEFIRST = 1 -- Monday
                BEGIN
                                 IF DATEPART(WEEKDAY, @date)  BETWEEN 1 AND 5
                                                BEGIN
                                                GOTO is_business
                                                END
                                 ELSE
                                                BEGIN
                                                GOTO is_not_business
                                                END
                END

IF @@DATEFIRST = 2 -- Tuesday
                BEGIN
                                 IF DATEPART(WEEKDAY, @date)  NOT IN (5, 6)
                                                BEGIN
                                                GOTO is_business
                                                END
                                 ELSE
                                                BEGIN
                                                GOTO is_not_business
                                                END
                END

IF @@DATEFIRST = 3 -- Wednesday
                BEGIN
                                 IF DATEPART(WEEKDAY, @date)  NOT IN (4, 5)
                                                BEGIN
                                                GOTO is_business
                                                END
                                 ELSE
                                                BEGIN
                                                GOTO is_not_business
                                                END
                END

IF @@DATEFIRST = 4 -- Thursday
                BEGIN
                                 IF DATEPART(WEEKDAY, @date)  NOT IN (3, 4)
                                                BEGIN
                                                GOTO is_business
                                                END
                                 ELSE
                                                BEGIN
                                                GOTO is_not_business
                                                END
                END

IF @@DATEFIRST = 5 -- Friday
                BEGIN
                                 IF DATEPART(WEEKDAY, @date)  NOT IN (2, 3)
                                                BEGIN
                                                GOTO is_business
                                                END
                                 ELSE
                                                BEGIN
                                                GOTO is_not_business
                                                END
                END

IF @@DATEFIRST = 6 -- Saturday
                BEGIN
                                 IF DATEPART(WEEKDAY, @date)  NOT IN (1, 2)
                                                BEGIN
                                                GOTO is_business
                                                END
                                 ELSE
                                                BEGIN
                                                GOTO is_not_business

                                                END
                END

is_business:
SELECT @return = 1
RETURN @RETURN

is_not_business:
SELECT @return = 0

RETURN @RETURN
END

Now we can experiment with various @@DATEFIRST values to ensure that our function works as expected:

SET DATEFIRST 4
SELECT dbo.fn_isbusinessday('Oct 5 01')

Result:
----
1

SET DATEFIRST 6
SELECT dbo.fn_isbusinessday('Sep 29 01')

Result:
----
0

In this article I introduced you to some of the powerful date related built-in functions provided with Microsoft SQL Server 2000. I showed you how the settings of the @@DATEFIRST and @@LANGUAGE global variables affects the values returned by these functions.

One of the welcomed additions to SQL Server programmer’s arsenal is the support of user-defined functions. If you need a function not provided with SQL Server, you can create your own user-defined function.