SQL Server: Determining Whether a Date is a Business Day
- Built-in Date-Related Functions
- The "Rough Draft"
- Research and Investigation
- Work in Progress
- Wrapping Up
Like this article? We recommend
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:
SELECT DATENAME(WEEKDAY, '9/25/00 11:05:00PM' ), DATENAME (MONTH, '9/25/00 11:05:00PM' ) Result: --------------------------- ------------------------------ Monday September
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.