Home > Articles > Data > SQL Server

  • Print
  • + Share This
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
  • + Share This
  • 🔖 Save To Your Account