Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Like this article? We recommend

Research and Investigation

For the program to be useful in all regional settings, you have to check for the @@DATEFIRST setting. Keep in mind, though, that as you change the @@DATEFIRST value, weekday numbers change as well.

For instance, if you decide to start the week on Saturday instead of Sunday, you'll get a different result with the same query as above:

SET DATEFIRST 6
SELECT DATEPART(WEEKDAY, '9/30/01')

Result:
----------- 
2

Therefore, to determine whether a particular date happens to be a business day, you need to account for each possible value of @@DATEFIRST and respective values for each weekday returned by the DATEPART function. The global variable @@DATEFIRST can take on any value between 1 and 7. You can investigate how the DATEPART function values change as you modify the value of @@DATEFIRST by executing the following code:

SET NOCOUNT ON   -- turn off counting of rows

-- create a table variable to store weekday names and numbers:
DECLARE @temp_table TABLE (
weekday_name VARCHAR(20), 
weekday_number TINYINT, 
DATEFIRST_value TINYINT)

-- declare and populate variables:
DECLARE @date SMALLDATETIME, @n TINYINT, @m TINYINT
SELECT @date = '9/30/01', @n = 1, @m = 1

/* run through all possible values of @@DATEFIRST and find
** appropriate weekday numbers:
*/

WHILE @n < 8
BEGIN
SET DATEFIRST @n
   WHILE @m < 8
   BEGIN
   INSERT @temp_table
   SELECT DATENAME(WEEKDAY, DATEADD(dd, @m, @date)), 
   DATEPART(WEEKDAY, DATEADD(dd, @m, @date)), 
   @@DATEFIRST
   SELECT @m = @m + 1
   END
SELECT @n = @n + 1, @m = 1
END

-- return the results:
SELECT * FROM @temp_table

Table 2 shows the results.

Table 2 Output of the Query

weekday_name

weekday_number

DATEFIRST_value

Monday

1

1

Tuesday

2

1

Wednesday

3

1

Thursday

4

1

Friday

5

1

Saturday

6

1

Sunday

7

1

Monday

7

2

Tuesday

1

2

Wednesday

2

2

Thursday

3

2

Friday

4

2

Saturday

5

2

Sunday

6

2

Monday

6

3

Tuesday

7

3

Wednesday

1

3

Thursday

2

3

Friday

3

3

Saturday

4

3

Sunday

5

3

Monday

5

4

Tuesday

6

4

Wednesday

7

4

Thursday

1

4

Friday

2

4

Saturday

3

4

Sunday

4

4

Monday

4

5

Tuesday

5

5

Wednesday

6

5

Thursday

7

5

Friday

1

5

Saturday

2

5

Sunday

3

5

Monday

3

6

Tuesday

4

6

Wednesday

5

6

Thursday

6

6

Friday

7

6

Saturday

1

6

Sunday

2

6

Monday

2

7

Tuesday

3

7

Wednesday

4

7

Thursday

5

7

Friday

6

7

Saturday

7

7

Sunday

1

7


  • + Share This
  • 🔖 Save To Your Account