SQL Server: Determining Whether a Date is a Business Day

Baya Pavliashvili demonstrates some of the built-in date-related functions and settings available in SQL Server through a user-defined function that helps you determine whether a given date falls on a business day.

Like this article? We recommend

Like this article? We recommend

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
nonbusiness days, certain organizations allow transactions only 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 you determine
whether a given date falls on a business day.

Built-in Date-Related Functions

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 two parameters: the part of the date
that you want 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 that you want to retrieve the week number of September
24, 2000. You can use the following code:

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

Similarly, if you want to know which weekday a particular date falls on, you
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 these examples, 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 like the DATEPART
function, the DATENAME function accepts two parameters: the portion of
the date that you want to retrieve and the date. The DATENAME function
can be used to retrieve any of the following: name of the year, quarter, month,
day of the year, day, week, weekday, hour, minute, second, or millisecond of the
specified date. For instance, you can determine the weekday name as well as the
month name of a given date as follows:

With previous releases of SQL Server, you could not create user-defined
functions; 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. Because SQL Server does not provide a built-in
function for determining whether a date falls on the business day, you can code
it yourself.