For reporting or for process management, it’s handy to be able to quickly identify weekdays and weekends. Here I’ll look at a few methods for calculating weekdays and weekends.

Some of these come easily from SQL Server, others require a bit more manipulation. Most of this builds on my previous articles on dates:

• Dates In SQL Server: Managing & Manipulating Dates
• Dates In SQL Server: Create Sample Date Ranges

Note: I’m going to look into public (bank) holidays and geographic variations in a later post.

Create sample date data

From my previous article, the below SQL will create a date range collection:

--------------------------------------------------------------------------------
-- Create Date Table
--------------------------------------------------------------------------------
CREATE TABLE date_t
(
  [TheDate]  DATE NOT NULL PRIMARY KEY
);

--------------------------------------------------------------------------------
-- Create Date list
-- Using Number sequence: https://datablog.roman-halliday.com/index.php/2018/06/01/dates-in-sql-server-create-sample-date-ranges/
--------------------------------------------------------------------------------
DECLARE @DateStart DATE = '01-JAN-17'; -- At time of writing this is the past
DECLARE @DateEnd   DATE = '01-JAN-19'; -- At time of writing this is the future
 
PRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);
PRINT 'Start End: '  + CONVERT(VARCHAR(10), @DateEnd,   120);

WITH number_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
), number_sequences AS (
SELECT t1.a AS t1a,
       t2.a AS t2a,
       t3.a AS t3a,
       (t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation
  FROM number_tbl t1
  CROSS JOIN number_tbl t2
  CROSS JOIN number_tbl t3
)
INSERT
  INTO date_t
      ([TheDate])
SELECT CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) AS [TheDate]
  FROM number_sequences
 WHERE CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) <= @DateEnd
 ORDER BY concatenate_calculation
;

Identifying the weekday and weekend

Weekends can be calculated by day name, or number. Both have their advantages and disadvantages:

• Day name is easier to read but less portable (different geographical settings use different names for the days).
• Day number is more portable, but also changes with configuration. The setting for DateFirst configures the first day of the week,  as I’ve demonstrated on rextester: SQL Server – DateFirst Example

Below I’m showing how to use both, I’ve not put in the regional settings as people will normally work in one language or another. I have included the DateFirst configuration. This makes a nice template which can assign the setting for the session.

SET DATEFIRST 1;

SELECT @@DateFirst AS [DateFirst Setting];

SELECT [TheDate],
       DateName(dw, [TheDate]) AS TheDate_day_name,
       DatePart(dw, [TheDate]) AS TheDate_day_of_week,
       CASE WHEN DatePart(dw, [TheDate]) IN (6,          7)        THEN 1 ELSE 0 END AS [IsWeekend], -- Using numeric day of week
       CASE WHEN DatePart(dw, [TheDate]) IN (6,          7)        THEN 0 ELSE 1 END AS [IsWeekday], -- Using numeric day of week
       CASE WHEN DateName(dw, [TheDate]) IN ('Saturday', 'Sunday') THEN 1 ELSE 0 END AS [IsWeekend], -- Using text of day name
       CASE WHEN DateName(dw, [TheDate]) IN ('Saturday', 'Sunday') THEN 0 ELSE 1 END AS [IsWeekday]  -- Using text of day name
  FROM date_t
;

See this in action on rextester: SQL Server – Weekdays and Weekends

Also see:

• DatePart
• DateName

Calculate working day number

To work out which weekday it is, in the month, I use windowing functions to add all the IsWeekday values up to and including the current date. In this instance I have partitioned by year and month so that I get rolling numbers for each month, but there is no reason this technique can’t be applied to any other period.

--------------------------------------------------------------------------------
-- Calculate working day number (of the month)
--------------------------------------------------------------------------------
SELECT [TheDate],
       DateName(dw, [TheDate]) AS TheDate_day_name,
       [IsWeekend],
       [IsWeekday],
       CASE [IsWeekday] WHEN 0 THEN NULL
                        ELSE SUM(CAST([IsWeekday] AS INT)) OVER (PARTITION BY YEAR([TheDate]), MONTH([TheDate]) ORDER BY [TheDate])
        END AS [WorkingDayNumber],
       CASE [TheDate] WHEN DateFromParts(YEAR(GetDate()), MONTH(GetDate()), DAY(GetDate())) THEN 'Today' ELSE '' END AS [DayMarker]
  FROM date_t
;

I’ve put this up on rextester: SQL Server – Calculate working day number