- 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
The "Rough Draft"
Now back to the task at hand: Most countries consider Monday through Friday as workdays and Saturday and Sunday as weekends. Therefore, to determine whether a date is a business day, all you have to do is use the DATENAME function with the WEEKDAY keyword. You could examine the output of the DATENAME function and see if it returns Saturday or Sunday, right? If so, you 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 you could call the function that you 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 you change your 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 returns 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 about modifying fn_isbusinessday function so that it fits any regional setting.
Because you can't use DATENAME in your user-defined function, you 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 DATEFIST 7
The catch is that @@DATEFIRST considers Monday to be day 1, Tuesday to be the day 2, and so forth. Therefore, if your @@DATEFIRST setting equals 7, the first day of the week is Sunday. On the other hand, when using the DATEPART function for retrieving weekday numbers, day 7 is Saturday (assuming that @@DATEFIRST is set to 7), with Sunday being day 1. For instance, the following query returns 1 because September 30, 2001, happens to fall on Sunday:
SELECT DATEPART(WEEKDAY, '9/30/01') Result: ----------- 1
Table 1 summarizes the differences between the values returned by @@DATEFIRST and the DATEPART function.
Table 1 Weekday Numbers for Various Settings of @@DATEFIRST
|
Day of Week |
Respective Value of @@DATEFIRST |
Value returned by the DATEPART function with the WEEKDAY keyword (When @@DATEFIRST = 7) |
|
Sunday |
7 |
1 |
|
Monday |
1 |
2 |
|
Tuesday |
2 |
3 |
|
Wednesday |
3 |
4 |
|
Thursday |
4 |
5 |
|
Friday |
5 |
6 |
|
Saturday |
6 |
7 |