Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Work in Progress
From these results, you can determine the weekday numbers for business days for each possible @@DATEFIRST value. Table 3 summarizes these facts.
Table 3 Business Days for Various Settings of @@DATEFIRST
|
@@DATEFIRST Value |
Business Day Numbers |
|
1 |
1–5 |
|
2 |
1–4, 7 |
|
3 |
1–3, 6–7 |
|
4 |
1, 2, 5–7 |
|
5 |
1, 4–7 |
|
6 |
3–7 |
|
7 |
2–6 |
Now that you have done all the preliminary work, it's time to modify your function fn_isbusinessday so that it will work regardless of the regional settings (see Listing 1).
Listing 1: The Master Program
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 you can experiment with various @@DATEFIRST values to ensure that your 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